Asked  7 Months ago    Answers:  5   Viewed   17 times

My iPhone app connects to my PHP web service to retrieve data from a MySQL database. A request can return 500 results.

What is the best way to implement paging and retrieve 20 items at a time?

Let's say I receive the first 20 ads from my database. Now how can I request for the next 20 ads?

 Answers

11

From the MySQL documentation:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Tuesday, June 1, 2021
 
van_folmert
answered 7 Months ago
94

You do not need to do that. You are using prepared statements, which escape the variables automatically.

Wednesday, March 31, 2021
 
laurent
answered 9 Months ago
81

When you use just "localhost" the MySQL client library tries to use a Unix domain socket for the connection instead of a TCP/IP connection. The error is telling you that the socket, called MySQL, cannot be used to make the connection, probably because it does not exist (error number 2).

From the MySQL Documentation:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

There are a few ways to solve this problem.

  1. You can just use TCP/IP instead of the Unix socket. You would do this by using 127.0.0.1 instead of localhost when you connect. The Unix socket might by faster and safer to use, though.
  2. You can change the socket in php.ini: open the MySQL configuration file my.cnf to find where MySQL creates the socket, and set PHP's mysqli.default_socket to that path. On my system it's /var/run/mysqld/mysqld.sock.
  3. Configure the socket directly in the PHP script when opening the connection. For example:

    $db = new MySQLi('localhost', 'kamil', '***', '', 0, 
                                  '/var/run/mysqld/mysqld.sock')
    
Tuesday, June 1, 2021
 
Anand
answered 7 Months ago
25

You'll have to create custom accessors if you want to restrict the values to an enum. So, first you'd declare an enum, like so:

typedef enum {
    kPaymentFrequencyOneOff = 0,
    kPaymentFrequencyYearly = 1,
    kPaymentFrequencyMonthly = 2,
    kPaymentFrequencyWeekly = 3
} PaymentFrequency;

Then, declare getters and setters for your property. It's a bad idea to override the existing ones, since the standard accessors expect an NSNumber object rather than a scalar type, and you'll run into trouble if anything in the bindings or KVO systems try and access your value.

- (PaymentFrequency)itemTypeRaw {
    return (PaymentFrequency)[[self itemType] intValue];
}

- (void)setItemTypeRaw:(PaymentFrequency)type {
    [self setItemType:[NSNumber numberWithInt:type]];
}

Finally, you should implement + keyPathsForValuesAffecting<Key> so you get KVO notifications for itemTypeRaw when itemType changes.

+ (NSSet *)keyPathsForValuesAffectingItemTypeRaw {
    return [NSSet setWithObject:@"itemType"];
}
Friday, June 11, 2021
 
Pupil
answered 6 Months ago
99

I tried directly connecting to the database using the JDBC driver for MySQL but my program is crashing so I'm not sure if Android "supports" the JDBC driver for MySQL.

Never never never use a database driver across an Internet connection, for any database, for any platform, for any client, anywhere. That goes double for mobile. Database drivers are designed for LAN operations and are not designed for flaky/intermittent connections or high latency.

Do I connect to a Java server program using sockets (or some other method of communication)?

It doesn't have to be Java. It just has to be something designed for use over the Internet. As Mr. King's comment suggests, Web services have been used for this for much of the past decade. For Android, REST Web services are probably the easiest to consume, since there is no built-in support for SOAP or XML-RPC. But whether the Web service is implemented in Java, or PHP, or Perl, or SNOBOL, is up to you.

Well, OK, perhaps SNOBOL won't be a viable option. :-)

Thursday, July 29, 2021
 
Uours
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share