Optimize my sql query

Question

I have couple auto increment s.no and respective names in my mysql database.

Currently the queries written are like below.

    $result = mysql_query ("SELECT name FROM table WHERE s.no= '1'") or die (mysql_error ());
    while($row = mysql_fetch_array($result)){

    $name1= $row['name'];
    }

    $result = mysql_query ("SELECT name FROM table WHERE s.no= '2'") or die (mysql_error ());
    while($row = mysql_fetch_array($result)){

    $name2= $row['name'];
    }

How could this be optimized, to have a single query and assign name1 from s.no=1, nameN from s.no=N??

Just two names, or N names? Also, the mysql_ extensions are deprecated in PHP 5.5; you should use PDO or MySQLi or something. — Ry-
– Ry- ♦, Commented Dec 25, 2012 at 17:24

Daniel A. White · Accepted Answer · 2012-12-25 17:23:32Z

5

You could do this with an IN clause.

 SELECT name, s.no as no FROM table WHERE s.no IN ('1', '2')

Then, dynamically look up the variable with the string.

$varName = 'name'.$row['no'];
$$varName = $row['name];

answered Dec 25, 2012 at 17:23

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S3ddi9 · Accepted Answer · 2012-12-25 17:26:15Z

0

$result = mysql_query ("SELECT name,s.no numb FROM table WHERE s.no= '1' OR s.no= '2'") or die (mysql_error ());
while($row = mysql_fetch_array($result)){

if($row['numb']==1)
   $name1= $row['name'];
else if($row['numb']==2)
   $name2= $row['name'];

}

answered Dec 25, 2012 at 17:26

S3ddi9

2,1713 gold badges20 silver badges34 bronze badges

Comments

ktm5124 · Accepted Answer · 2012-12-25 17:29:30Z

0

Pardon my PHP, but something like this would work.

$name = [];

$result = mysql_query ("SELECT s.no, name FROM table") or die (mysql_error ());
while($row = mysql_fetch_array($result)) {
    $index = $row['no'];
    $name[$index] = $row['name'];
}

I had to do something similar recently in java where I used a Map to keep track of logical "groups" in a batch query. The code above uses the same idea.

If you're only looking to get rows for s.no in (1, 2), then as the poster above says you can use an IN clause.

answered Dec 25, 2012 at 17:29

ktm5124

12.2k23 gold badges86 silver badges130 bronze badges

Comments

goat · Accepted Answer · 2012-12-25 17:43:05Z

0

$sql = "SELECT name FROM table WHERE s.no IN ('1', '2') order by s.no asc";
$result = mysql_query($sql);
$name1 = mysql_result($result, 0, 0);//fetches row 0, column 0
$name2 = mysql_result($result, 1, 0);//fetches row 1, column 0

http://php.net/manual/en/function.mysql-result.php

answered Dec 25, 2012 at 17:43

goat

31.9k7 gold badges76 silver badges98 bronze badges

Collectives™ on Stack Overflow

Optimize my sql query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related