Jump to content


Photo

[SOLVED] MYSQL, PHP ARRAY, FOR LOOP


  • Please log in to reply
2 replies to this topic

#1 bluebyyou

bluebyyou
  • Members
  • PipPipPip
  • Advanced Member
  • 234 posts
  • LocationIllinois

Posted 29 September 2005 - 10:24 AM

I have a table in my database names Classes, which stores, pretty obviously the classes that I have taken. the fields are department, course, name, description, and semester. I am trying to run a query to display the courses. Eventually I will have a drop down box to change the query based on the semester. Anyways, after the query i want to dump the info into a table, that im generating with php. I have the part down with using a for loop so i can pull each row out of the database and put it into a separate row in the html table. What I am having trouble with is getting the info out of the array from the query.

Heres what I have going so far, that only works to give me a empty table. Im kind of stuck now.
[code=auto:0]
<?php

//Database Info
$host="mysql.wiu.edu:****";
$user="****";
$password="******";
$database="mydb";
$connection = mysql_connect($host,$user,$password) or die ("Could Not Connect to the server.");
$db = mysql_select_db($database,$connection) or die ("Could Not Connect to the database.");
$sql = "SELECT * FROM Classes WHERE semester = '2'";
$query = mysql_query($sql) or die ("Couldn't Execute Query");
$row = mysql_fetch_array($query,MYSQL_NUM);
ECHO "<table>\n";
extract($row);

for ($i=0;$i<=6;$i++)
{
ECHO "<tr>\n";
ECHO "<td>$dept[$i]</td>\n";
ECHO "<td>$crse[$i]</td>\n";
ECHO "<td>$name[$i]</td>\n";
ECHO "<td>$description[$i]</td>\n";
ECHO "<td>$semester[$i]</td>\n";
ECHO "</tr>\n";
}
ECHO "</table>";

?>
As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by an invention of ours." - Ben Franklin

#2 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 29 September 2005 - 05:03 PM

You wrote:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php
$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT * FROM Classes WHERE semester = \'2\'\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#DD0000\"]\"Couldn\'t Execute Query\"[/span][span style=\"color:#007700\"]);
[/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"],[/span][span style=\"color:#0000BB\"]MYSQL_NUM[/span][span style=\"color:#007700\"]);
ECHO [/span][span style=\"color:#DD0000\"]\"<table>\n\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]extract[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$row[/span][span style=\"color:#007700\"]);

for ([/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"];[/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]<=[/span][span style=\"color:#0000BB\"]6[/span][span style=\"color:#007700\"];[/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]++)
{
ECHO [/span][span style=\"color:#DD0000\"]\"<tr>\n\"[/span][span style=\"color:#007700\"];
ECHO [/span][span style=\"color:#DD0000\"]\"<td>$dept[$i]</td>\n\"[/span][span style=\"color:#007700\"];
ECHO [/span][span style=\"color:#DD0000\"]\"<td>$crse[$i]</td>\n\"[/span][span style=\"color:#007700\"];
ECHO [/span][span style=\"color:#DD0000\"]\"<td>$name[$i]</td>\n\"[/span][span style=\"color:#007700\"];
ECHO [/span][span style=\"color:#DD0000\"]\"<td>$description[$i]</td>\n\"[/span][span style=\"color:#007700\"];
ECHO [/span][span style=\"color:#DD0000\"]\"<td>$semester[$i]</td>\n\"[/span][span style=\"color:#007700\"];
ECHO [/span][span style=\"color:#DD0000\"]\"</tr>\n\"[/span][span style=\"color:#007700\"];
}
ECHO [/span][span style=\"color:#DD0000\"]\"</table>\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]
To fix your problems using your method you have to replace $dept, $crse, etc., with $row, since that's were your data is, and echo out one field at a time. Also don't use the extract() function.

Something like this:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php
$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT * FROM Classes WHERE semester = \'2\'\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#DD0000\"]\"Couldn\'t Execute Query\"[/span][span style=\"color:#007700\"]);
echo [/span][span style=\"color:#DD0000\"]\"<table>\n\"[/span][span style=\"color:#007700\"];
while([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_array[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"],[/span][span style=\"color:#0000BB\"]MYSQL_NUM[/span][span style=\"color:#007700\"])) {
        echo [/span][span style=\"color:#DD0000\"]\"<tr>\n\"[/span][span style=\"color:#007700\"];
        for ([/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]=[/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"];[/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]<=[/span][span style=\"color:#0000BB\"]6[/span][span style=\"color:#007700\"];[/span][span style=\"color:#0000BB\"]$i[/span][span style=\"color:#007700\"]++)
                  echo [/span][span style=\"color:#DD0000\"]\"<td>$row[$i]</td>\n\"[/span][span style=\"color:#007700\"];
        echo [/span][span style=\"color:#DD0000\"]\"</tr>\n\"[/span][span style=\"color:#007700\"];
}
echo [/span][span style=\"color:#DD0000\"]\"</table>\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

If I were coding this, I would use the mysql_fetch_assoc() function, since, I think, it results in more understandable code:
[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?php
$sql [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT * FROM Classes WHERE semester = \'2\'\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$sql[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#DD0000\"]\"Couldn\'t Execute Query\"[/span][span style=\"color:#007700\"]);
echo [/span][span style=\"color:#DD0000\"]\"<table>\n\"[/span][span style=\"color:#007700\"];
while ([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_fetch_assoc[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$query[/span][span style=\"color:#007700\"]))
echo [/span][span style=\"color:#DD0000\"]\"<tr>\n\"[/span][span style=\"color:#007700\"];
foreach ([/span][span style=\"color:#0000BB\"]$row [/span][span style=\"color:#007700\"]as [/span][span style=\"color:#0000BB\"]$k[/span][span style=\"color:#007700\"]=>[/span][span style=\"color:#0000BB\"]$val[/span][span style=\"color:#007700\"])
        echo [/span][span style=\"color:#DD0000\"]\"<td>$val</td>\n\"[/span][span style=\"color:#007700\"];
echo [/span][span style=\"color:#DD0000\"]\"</tr>\n\"[/span][span style=\"color:#007700\"];
}echo [/span][span style=\"color:#DD0000\"]\"</table>\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#0000BB\"]?>[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

Ken

#3 bluebyyou

bluebyyou
  • Members
  • PipPipPip
  • Advanced Member
  • 234 posts
  • LocationIllinois

Posted 29 September 2005 - 11:50 PM

Excellent, that worked, thank you.
As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by an invention of ours." - Ben Franklin




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users