Jump to content

SQL query using prepared statements


anderson_catchme

Recommended Posts

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
 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  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);
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

 

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. 

Link to comment
Share on other sites

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 by anderson_catchme
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.