0

My task is essentially to make a dropdown list, using the <option> tag, out of a table in a database. To simplify it, I have created a MySQL procedure that will output the value based on its ranking in the table. The procedure has a parameter id since the output is passed on to the <option> tag. Hence, each <option> tag will fetch the single row outputted by the procedure based on its id in the database.

I know it's not the ideal implementation, though, but I'm working through it so I can even improve it once the basics are working. With my current implementation, no data is fetched from the database any more after the loop is run once. Here is the php code:

$counter=1;
while ($exec2>0)
{   
    $res = mysql_query("CALL PROMOSTAFF_RANKING($counter);");
    $exec3 = mysql_fetch_array($res);
    $fname = $exec3[0];
    $lname = $exec3[1];
    echo '<option value="';echo $fname; echo ' '; echo $lname;echo '" >';echo $fname; echo ' '; echo $lname; echo'</option>'; 
    $exec2--;
    $counter++;
}

In the html, only the first dropdown option displays correctly, while the second and third (since there should be 3 options) are blank. I have outputted values of $counter and $exec2 for the second and third options, and they have the correct values to be able to run the procedure. However, I guess after the first iteration of the loop, no value is fetched to $fname and $lname.

Can anybody enlighten me on this? I would love to learn more and improve the codes with your inputs. Thanks!

Mr Lister
  • 45,515
  • 15
  • 108
  • 150
Lane
  • 366
  • 1
  • 2
  • 11
  • 4
    You want to improve the code? For starters, use a database function that will actually work in a current version of PHP: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – miken32 Jan 24 '16 at 18:35
  • 2
    Also, building a stored procedure so that you have to make a database call in every iteration of your loop is hardly simplifying anything! – miken32 Jan 24 '16 at 18:36
  • Does the PROMOSTAFF_RANKING procedure return more than one tuple (record) when it is called? – hherger Jan 24 '16 at 18:39
  • It only returns one row since it has a parameter receiving the id of the record. This is so the number of options will also iterate based on the number of records. If I am already in the second option, I will output the second record in the table, using the procedure. – Lane Jan 24 '16 at 18:40
  • I suspect this is a classic XY problem. Your stored procedure is likely an unnecessary complication that's preventing you from using normal row-based database iteration. – miken32 Jan 25 '16 at 00:01
  • (1 query returning N rows) is more efficient than (N queries of 1 row) use the SQL to get your data, then use PHP to output it - by iterating over the result of the (single) query. – Paul Maxwell Jan 31 '16 at 11:56

0 Answers0