CODEWITHSUNDEEP
phpmysqlvariables
kalaba2003
kalaba2003

Reputation: 1229

How can I assign a variable's value to another variable which comes before?

My code is here :

$array_letter = array("A","B","C","Ç","D","E","F","G","H","I","İ","J","K","L",
                      "M","N","O","P","R","S","Ş","T","U","Ü","V","Y","Z");

$sql = "SELECT id,city  FROM city WHERE city LIKE '" .$array_letter[$i]."%'";

And after these codes :

for ($i=0;$i<27;$i++) {
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {

 echo "<h3>".$row['city']."</h3>";

   }    
}

$sql is meaningless because $array_letter[$i]will not work there. But $sql must be top of these codes for design. Because I coded switch-case statement. According to requests, $sql will change for this reason I can not write $sql under for loops. But all my queries depens on $array_letter. How can I make $array_letter work?

Upvotes: 2

Views: 1786

Answers (3)

Muthukumar M
Muthukumar M

Reputation: 1126

This will help to reduce the database calls.

$array_letter = array("A","B","C","Ç","D","E","F","G","H","I","İ","J","K","L",
                  "M","N","O","P","R","S","Ş","T","U","Ü","V","Y","Z");
for($i=0;$i<count($array_letter);$i++){
if($i!=count($array_letter)-1)
$qstring.="city like '".$array_letter[$i]."%' or ";
 else
$qstring.="city like '".$array_letter[$i]."%'";
}
$sql = "SELECT id,city  FROM city WHERE ".$qstring;
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
echo "<h3>".$row['city']."</h3>";
  }

Upvotes: 0

Matthew
Matthew

Reputation: 48284

You should use the mysqli driver and prepared statements:

$st = $mysqli->prepare("SELECT id,city FROM city WHERE city LIKE ?'");
for ($i=0;$i<27;$i++) {
  $st->bind_param("s", $array_letter[$i].'%');
  $st->execute();
  $result = $st->get_result();

  while ($row = $result->fetch_assoc()) {
    echo "<h3>".$row['city']."</h3>";
  }  
}

Although for this case, I would recommend just doing one big query since it looks like you are getting everything: SELECT id,city FROM city ORDER BY city...

For educational purposes, an alternative approach would be to do something like:

$sql = "SELECT * FROM foo WHERE bar='%s'";
mysql_query(sprintf($sql, "42"));

That can be useful in other situations, but again, if you are writing SQL, use prepared statements as they solve this problem more gracefully with the extra protection of helping to prevent SQL injection attacks and minimizing the amount of SQL parsing the server has to do.

Upvotes: 3

Ja͢ck
Ja͢ck

Reputation: 173562

You should use prepared statements, as Matthew mentioned in his answer.

Otherwise consider this (using PHP 5.3 closures):

$sql = function($i) use ($array_letters) {
    return "SELECT id,city  FROM city WHERE city LIKE '" .$array_letter[$i]."%'";
}

Then inside your loop:

mysql_query($sql($i));

Upvotes: 1

Related Questions