Mysql parameterized queries in php

Question

I have a php code for some MYSQL interogations,

Code is:

      $DBTYPE = 'mysql';
      $DBHOST = 'localhost';
      $DBUSER = 'tuser';
      $DBPASSWORD = 'password';
      $DBNAME = 'dbname';
      $link = mysql_connect($DBHOST, $DBUSER, $DBPASSWORD);
      mysql_select_db($DBNAME); 
      if (!$link) {
          die('Could not connect: ' . mysql_error());
      }
 //IMG**0**
       $hotelc = $hotelCodes[**0**];    
      $result = mysql_query("SELECT ImageURL FROM Flat_table where HotelCode= '$hotelc'", $link);
  if(!$result) {
      die("Database query failed: " . mysql_error());
  }
  while ($row = mysql_fetch_array($result)) {
      $ImageURL**0** = $row["ImageURL"];
  }
  //IMG**1**
       $hotelc = $hotelCodes[**1**];    
      $result = mysql_query("SELECT ImageURL FROM Flat_table where HotelCode= '$hotelc'", $link);
  if(!$result) {
      die("Database query failed: " . mysql_error());
  }
  while ($row = mysql_fetch_array($result)) {
      $ImageURL**1** = $row["ImageURL"];
  }
..........................
//IMG**x**
       $hotelc = $hotelCodes[**x**];    
      $result = mysql_query("SELECT ImageURL FROM Flat_table where HotelCode= '$hotelc'", $link);
  if(!$result) {
      die("Database query failed: " . mysql_error());
  }
  while ($row = mysql_fetch_array($result)) {
      $ImageURL**x** = $row["ImageURL"];
  }

The repeating value on each code line is bolded.

How can i create a Mysql parameterized queries in php.n to avoid write all the lines .I need to extract ~100 $ImageURL from the Flat_table where the $hotelc is found.

**Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).** They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). **Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement)** instead, and **use [PDO](http://us1.php.net/pdo).** — Jay Blanchard, Jan 20 '15 at 14:55
When PHP 7 is released, the `mysql_` functions will be removed entirely — Machavity, Jan 20 '15 at 15:01

score 0 · Answer 1 · answered Jan 20 '15 at 15:02

0

For example you have to repeat it $N times:

for($i=0; $i<$N; $i++)
{
    $hotelc = $hotelCodes[ $i ];    
    $result = mysql_query("SELECT ImageURL FROM Flat_table where HotelCode= '$hotelc'", $link);
    if(!$result) {
        die("Database query failed: ".mysql_error());
    }
    while ($row = mysql_fetch_array($result)) {
        ${'ImageURL'+$i} = $row["ImageURL"];
    }
}

answered Jan 20 '15 at 15:02

Oleg Dubas

2,320
1
10
24

That's why we have arrays :-) – jeroen Jan 20 '15 at 15:03
1

So... if he has 1000 records we need 1000 queries? – Machavity Jan 20 '15 at 15:04
Yes, 1000 records = 1000 queries in your current logic. – Oleg Dubas Jan 20 '15 at 15:05
And how would you know how many images to display later on for each record? – jeroen Jan 20 '15 at 15:06
This is why we use arrays :) Your logic is ill . The question was — how to rewrite it to avoid repetitions. Maybe you need to rewrite the logic from the very beginning – Oleg Dubas Jan 20 '15 at 15:07

score 0 · Answer 2 · answered Jan 20 '15 at 15:05

To loop, use for:

for($n=0; $n<100; $n++){
  $hotelc = $hotelCodes[$n];    
  $result = mysql_query("SELECT ImageURL FROM Flat_table where HotelCode= '$hotelc'", $link);
  if(!$result) {
      die("Database query failed: " . mysql_error());
  }
  while ($row = mysql_fetch_array($result)) {
      $ImageURL[$n] = $row["ImageURL"];
  }
}

But the inner functions inside the loop is inefficient because mysql query will be executed 100 times. You can query all the ImageURL by using IN() syntax in mysql:

//Wrap all hotelCodes into one string for query, like ["a","b"] to "'a','b'"
$len = count($hotelCodes);
foreach($hotelCodes as $key=>$code){
 $hotelCodes[$key] = "'".$code."'";
}

$codesStr = implode(",", $hotelCodes);

$result = mysql_query("SELECT ImageURL FROM Flat_table where HotelCode IN (".$codeStr.")", $link);

//Other things...

score 0 · Answer 3 · answered Jan 20 '15 at 15:11

When writing a function, you look for the commonality. Furthermore, you want to minimize the database interaction. I am, for the sake of deprecation, going to assume $link uses mysqli_connect()

$ImageURL = array();
$list = implode('", "', $hotelCodes);
$result = mysqli_query($link, 'SELECT ImageURL FROM Flat_table where HotelCode IN "' . $list . '"');
while($row = mysql_fetch_assoc($result)) {
     $ImageURL[] = $row["ImageURL"];
}

This runs only one query and then loops through the results, generating an associative array. So echo $ImageURL[0]; will output your first URL.

Mysql parameterized queries in php

3 Answers3