SQL query using prepared statements

[anderson_catchme]

class getjson {
function query_db ($mysqli, $search_string){
    $query = "SELECT name, location, address FROM csv_table WHERE address LIKE CONCAT('%',?,'%')
    OR name LIKE CONCAT('%',?,'%')
    OR location LIKE CONCAT('%',?,'%') LIMIT 0,5";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("sss", $search_string, $search_string, $search_string);
    $stmt->execute();
//    $stmt->bind_result($name, $location, $address); // not necessary for code to work
    $result = $stmt->get_result();
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
        foreach ($row as $value)
          {
                    $data[] = array(
                    'label' => $value .', '. $value.' '. $value ,
                    'value' => $value );
            }

        }
                return $data;
    }
}
$results = new getjson;
$results = $results->query_db($mysqli, $search_string);
print_r($results);
echo count($results);

 

 

I have the following code. It works, but I cannot get array notation working correctly. For instance, I would like to access $row['address'] to input into my array in the foreach statement. But I can't seem to get it to work after a couple hours. So any ideas appreciated.

 

Thanks, Mark
 

[ginerjm]

Your foreach looks bad.  You get a row of 3 fields and then you create an array that will yield

this array :

 

array('label' = 'name1,name1,name1','value' = 'name1'),array('label' = 'location1,location1,location1','value'='location1'),array('label'='address1,address1,address1','value'='address1'))

 

for each row in your query results and then you return that.

 

I think you want:

 

  while (list($name,$loc,$addr)= mysqli_fetch_array($result, MYSQLI_NUM))
   {
          $data[] = array('name'=$name,'loc'=>$loc,'addr'=>$addr);
   }

 

Of course all you are doing here is taking one array ($result - although technically it's called a resource) and turning it into another array($data).

What's the point of that?

[anderson_catchme]

Thanks alot! It worked perfectly.

[Jacques1]

  while (list($name,$loc,$addr)= mysqli_fetch_array($result, MYSQLI_NUM))
   {
          $data[] = array('name'=$name,'loc'=>$loc,'addr'=>$addr);
   }

 

You fetch the row as a numerical array, extract the values and put them into an associative array? Why not fetch an associative array in the first place? Or rather: Why not simply fetch the entire result set? That's one line of code for everything:

$result = mysqli_fetch_all($result, MYSQLI_ASSOC);

[ginerjm]

 

You fetch the row as a numerical array, extract the values and put them into an associative array? Why not fetch an associative array in the first place? Or rather: Why not simply fetch the entire result set? That's one line of code for everything:

 

 I did so to show the OP his mistake.  I also pointed out and asked the question - Why are you doing this - which is what you have also now posted.

[jazzman1]

 

Why not fetch an associative array in the first place?

B/s for the beginners is much more descriptive and easier to understand what's going on behind the scene, but what about performance and memory consumption.

 

Here's my test with 500 000 records using a pdo driver and firebird for a testing database server. 

 

1.

while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
    
  $data[] = array($row[0],$row[1],$row[2]);
  
}

MemoryUsage: 414479kb
CountRows: 500000
ExecutionTime: 3.088 seconds

2.

$row = $stmt->fetchAll(PDO::FETCH_NUM);

MemoryUsage: 414490kb
CountRows: 500000
ExecutionTime: 2.011 seconds

It's true that the memory usage is a little bit more but the execution time is much, much better using the internal pdo fetchAll method. 

[anderson_catchme]

B/s for the beginners is much more descriptive and easier to understand what's going on behind the scene, but what about performance and memory consumption.

 

Here's my test with 500 000 records using a pdo driver and firebird for a testing database server. 

 

1.

while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
    
  $data[] = array($row[0],$row[1],$row[2]);
  
}

MemoryUsage: 414479kb
CountRows: 500000
ExecutionTime: 3.088 seconds

2.

$row = $stmt->fetchAll(PDO::FETCH_NUM);

MemoryUsage: 414490kb
CountRows: 500000
ExecutionTime: 2.011 seconds

It's true that the memory usage is a little bit more but the execution time is much, much better using the internal pdo fetchAll method. 

 

 

Fair enough. But how would I use $row = $stmt->fetchAll(); given my script? What would the while statement look like and could I use array notation for my column names?

 

Thanks for everyone's input.

Edited August 18, 2014 by anderson_catchme

[Jacques1]

First of all, forget about the performance stuff. Some members of this forum have developed a certain fetish for randomly coming up with dubious micro-optimizations, but that shouldn't bother you. Worry about readability, not “performance”.

 

You do not use fetchAll() together with a while loop. Like the name already says, this method fetches all rows in one go. The $row variable is simply a misnomer by jazzman1. Since the return value is the entire result set, so you would name it $addresses or something like that. You should generally avoid meaningless variables like $row or $data. What “row”, what “data”?

 

Last but not least, always fetch associative arrays so that you can reference the columns by their name. I have no idea why the others used numerical arrays (maybe the fetish again), but you don't want that. I can assure you that it's friggin' annoying if you constantly have to go back to the query to figure out what on earth $row[12] actually means.

[jazzman1]

 

First of all, forget about the performance stuff. Some members of this forum have developed a certain fetish for randomly coming up with dubious micro-optimizations, but that shouldn't bother you. Worry about readability, not “performance”.

 

@Jacques1, did you sleep well last night?

 

Of course, we do care about the performance and everyone should care very well about the good coding techniques and programming practices. So, 42.2436% difference is it nothing for you?...and you call this "dubious micro-optimizations"? I wrote this little benchmark and I did run it on two different machines.

 

 

The $row variable is simply a misnomer by jazzman1....

 

You do realize that my example is not applied to OP's script at all, even more in my testing I used the PDO instead MySQLi library. Also there is a convention variable names often using in programming like - @stmt, @dbh, @sql, @row, @data, @query and so on and so forth... but wait, how about your example - $result = mysqli_fetch_all($result, MYSQLI_ASSOC);

 

 

I have no idea why the others used numerical arrays (maybe the fetish again)...

 

B/s of size of allocated memory. PhP allocates more memory using litteral indexes, so in some cases is good in some not depends on the hardware. Anyways.., I just followed in ginerjm's example.

 

To OP, "@ how would I use $row = $stmt->fetchAll(); given my script?"

 

You can't use the pdo::fetchAll method in your script you need to use mysqli_result::fetch_all one!