looping through tables

[woodplease]

i have some code which loops through a table, and displays the results. i have then added extra code so that it loops through another table, and prints out the reults, that are related to the first loop. so the page should look like

 

menu

  submenu 1

  submenu 2

  submenu 3

menu 2

  submenu 4 etc

The problem is that the code only prints out the first row from the first loop and nothing else.

<?php
$list = "SELECT * FROM section_main";
$result = mysql_query($list) or die ("Query failed");

$numofrows = mysql_num_rows($result);
echo "<table border='1' id='section_list'>";
echo "<tr><th>section_id</th><th>section_title</th></tr>";
for($j = 1; $j < $numofrows; $j++) { 
    echo '<tr>';
$row = mysql_fetch_array($result);  
    echo "<td>". $row['section_id'] . "</td><td>". $row['section_title'] . "</td>";

$query = "SELECT section_sub.section_sub_title, section_sub.section_title WHERE 		section_sub.section_title = " .$row['section_title']."ORDER BY section_sub_title";

$result2 = mysql_query($query) or die ("query failed2");//This part does not work
$numofrows2 = mysql_num_rows($result2);

for($i = 0; $i<$numofrows2; $i++){
	$row2 = mysql_fetch_array($result2);
	echo '<tr>'.$row2['section_sub_title'].'';
}
}
echo "</tr>";

echo '</table>';
?>

 

Any help on whats wrong would be great

[shlumph]

I can see this producing an HTML mess, since you're constantly opening a row, without closing it. And the rows have no cells (td):

 

<?php

for($i = 0; $i<$numofrows2; $i++){
  $row2 = mysql_fetch_array($result2); 
  echo '<tr>'.$row2['section_sub_title'].'';
}

 

 

[woodplease]

i havnt completed all of the html yet, i just wanted to get the php working. any ideas on why the query failed?

[bulrush]

Your SELECT in $query does not include a FROM clause. It should say something like "SELECT field1, field2 FROM tablename WHERE foo=bar;".

 

Don't forget that trailing semicolon in SQL.

 

[woodplease]

i dont need a FROM clause, because of the format i've used  table.tablefield.

I've changed it so that it does include a from clause, but it has no effect.

[wildteen88]

You will be better off using an SQL Join. Have a look at this post for an example I posted earlier.

[woodplease]

if i were to use a join, how would i get it to display in the format i want

i.e.

 

menu

  submenu

  submenu

menu

  submenu.

 

in the 'submenu' table, i have a foreign key of the 'menu' table. i'm using this field as a link between the 2

[wildteen88]

What is your table structure? and some example data

[woodplease]

section_main table

 

 

   

section_id

   

int

 

 

   

section_title

   

varchar

 

 

 

section_sub

 

   

section_sub_id

   

int

 

 

   

section_sub_title

   

varchar

 

 

   

section_sub_desc

   

varchar

 

 

   

section_title

   

varchar

 

 

in the section_sub table, the section_title relates to the section_title in the section_main table, if that makes any sense. thats how i've linked the two tables

 

e.g.

 

   

section_id

   

section_name

 

 

   

1001

   

films

 

 

 

   

section_sub_id

   

section_sub_title

   

section_sub_desc

   

section_title

 

 

   

101

   

Star Wars

   

scifi

   

films

 

 

   

102

   

avatar

   

scifi

   

films

 

 

   

103

   

bad boys

   

action

   

films

 

 

i want it so that it would be

 

movies

  star wars

  avatar

  bad boys

 

tvshows

  supernatural

  etc

 

 

[wildteen88]

Test the following code

<?php
$query = 'SELECT m.section_id,
       m.section_title,
       s.section_sub_title,
       s.section_sub_desc
FROM section_main AS m
LEFT JOIN section_sub as s ON s.section_title = m.section_title
ORDER BY s.section_id, s.section_sub_title ASC';
$result = mysql_query($query);

$data = array();
while($row = mysql_fetch_assoc($result))
    $data[$row['section_title']][] = $row;
?>

<dl>
<?php foreach($data as $section_title => $section): ?>
    <dt><h1><?php echo $section_title; ?></h1></dt>
    <?php foreach($section as $sub_section): ?>
        <dd><?php echo $sub_section['section_sub_title']; ?></dd>
    <?php endforeach; ?>
<?php endforeach; ?>
</dl>

[woodplease]

the code gives me the error

"Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\Users\Alex\Desktop\xampp\htdocs\SciFiStorm\forum\test2.php on line 23"

 

 

<?php
$query = 'SELECT m.section_id,
       m.section_title,
       s.section_sub_title,
       s.section_sub_desc
FROM section_main AS m
LEFT JOIN section_sub as s ON s.section_title = m.section_title
ORDER BY s.section_id, s.section_sub_title ASC';
$result = mysql_query($query);

$data = array();
while($row = mysql_fetch_assoc($result)) //thus is where it finds the error
    $data[$row['section_title']][] = $row;
?>

<dl>
<?php foreach($data as $section_title => $section): ?>
    <dt><h1><?php echo $section_title; ?></h1></dt>
    <?php foreach($section as $sub_section): ?>
        <dd><?php echo $sub_section['section_sub_title']; ?></dd>
    <?php endforeach; ?>
<?php endforeach; ?>
</dl>

 

[mikosiko]

$result = mysql_query($query) or die("Select Error: " . mysql_error());

 

and check the error.

 

[woodplease]

the error given is " Select Error: Unknown column 's.section_id' in 'order clause'  "

[mikosiko]

post the structure of your tables (both)... the error is telling you exactly what is happening.. just check your tables and look if the select is consistent with their definition.

[woodplease]

The two tables used are

 

section_main table

 

   

section_id

   

int

 

 

   

section_title

   

varchar

 

 

 

section_sub table

 

   

section_sub_id

   

int

 

 

   

section_sub_title

   

varchar

 

 

   

section_sub_desc

   

varchar

 

 

   

section_title

   

varchar

 

 

 

I cant find anything wrong with the sql

[mikosiko]

....

 

I cant find anything wrong with the sql

 

well.. look again... first your tables and then your select... and then the error

 

 " Select Error: Unknown column 's.section_id' in 'order clause'  "

 

do you see any section_id field in your section_sub table ? 

[woodplease]

there is no section_id in the section_sub table, only section_title. i dont really understand the code given to me, but as i'm getting a similar error, obviously its similar to my code, which has the same problem. can you see any problems in my code?

<?php
$list = "SELECT * FROM section_main";
$result = mysql_query($list) or die ("Query failed");

$numofrows = mysql_num_rows($result);
echo "<table border='1' id='section_list'>";
echo "<tr><th>section_id</th><th>section_title</th></tr>";
for($j = 0; $j < $numofrows; $j++) { 
    echo '<tr>';
$row = mysql_fetch_array($result);  
    echo "<tr><td>". $row['section_id'] . "</td><td>". $row['section_title'] . "</td></tr>";

$answer = $row['section_title'];

$query2 = "SELECT * FROM section_sub WHERE section_title = ".$answer." ORDER BY section_sub_title";

$result2 = mysql_query($query2) or die("Select Error :" . mysql_error()); 
$numofrows2 = mysql_num_rows($result2);

for($i = 0; $i<$numofrows2; $i++){
	$row2 = mysql_fetch_array($result2);
	echo '<tr><td>'.$row2['section_sub_title'].'</td></tr>';
}
}


echo '</table>';
?>

 

[mikosiko]

but as i'm getting a similar error, obviously its similar to my code

no even close to the error that your code is giving to you... nor similar... but well...

 

change this line... in your code

 

$query2 = "SELECT * FROM section_sub WHERE section_title = ".$answer." ORDER BY section_sub_title";

 

to this

 

$query2 = "SELECT * FROM section_sub WHERE section_title = '".$answer."' ORDER BY section_sub_title";

 

notice that I just added a single ' before and after the " surrounding $answer 

 

[woodplease]

right, the error has gone, but now it just displays an empty table

 

i know this must be getting annoying, but i just cant seem to find out whats wrong

[mikosiko]

ok... one more try...

 

do this modifications in your code (commented with ADD THIS LINE ... 2 lines)

 

<?php
$list = "SELECT * FROM section_main";
$result = mysql_query($list) or die ("Query failed");

$numofrows = mysql_num_rows($result);

echo "Select 1 Rows :" . $numofrows;  // ADD THIS LINE 

echo "<table border='1' id='section_list'>";
echo "<tr><th>section_id</th><th>section_title</th></tr>";
for($j = 0; $j < $numofrows; $j++) { 
    echo '<tr>';
$row = mysql_fetch_array($result);  
    echo "<tr><td>". $row['section_id'] . "</td><td>". $row['section_title'] . "</td></tr>";

$answer = $row['section_title'];

$query2 = "SELECT * FROM section_sub WHERE section_title = ".$answer." ORDER BY section_sub_title";

$result2 = mysql_query($query2) or die("Select Error :" . mysql_error()); 
$numofrows2 = mysql_num_rows($result2);

       echo "Select 2 Rows :" . $numofrows2;  // ADD THIS LINE

for($i = 0; $i<$numofrows2; $i++){
	$row2 = mysql_fetch_array($result2);
	echo '<tr><td>'.$row2['section_sub_title'].'</td></tr>';
}
}


echo '</table>';
?>

 

and analyze what numbers those echoes gave to you... and after that look your real data for both tables and try to understand what is happening.

 

 

[wildteen88]

woodplease: Try my code again but change ORDER BY s.section_id, to ORDER BY m.section_id,

 

You'll be better of with an SQL Join, that is why I postedthe example code earlier. But I had typo in the query.

[mikosiko]

woodplease: Try my code again but change ORDER BY s.section_id, to ORDER BY m.section_id,

 

You'll be better of with an SQL Join, that is why I postedthe example code earlier. But I had typo in the query.

 

I totally agree with you... but the OP doesn't understand it and apparently no willing to try... could be a good learning experience for him. 

 

and I don't want even mention  his data model flaws ... that will make his head spin more  :-\

[woodplease]

woodplease: Try my code again but change ORDER BY s.section_id, to ORDER BY m.section_id,

 

You'll be better of with an SQL Join, that is why I postedthe example code earlier. But I had typo in the query.

 

http://www.phpfreaks.com/forums/index.php/topic,308975.msg1459924.html#msg1459924

 

lookin at that example, where does the "u" come from, in u.user_id,  u.name, and ui.info?

 

[mikosiko]

table alias

 

like in "....  FROM users u ....  "  u is the alias for the users table

[wildteen88]

u is an alias to the Users table and ui is an alias to the users_info table. The alias can be anything, you can give a table/field an alternative name (alias). The alias is set directly after the table/field name.

 

When I have to reference a table name within a query I usually just use the initials of the table rather than having to type the whole table name each time. It is best practice when using Joins to reference the table and field name, others MySQL may report that a field name within your query is too ambiguous. This will happen if you two or more tables that have the same field name.

 

Without the aliases the query would be

$query = "SELECT users.user_id,
                 users.name,
                 users_info.info
          FROM users
          RIGHT JOIN users_info
            ON users_info.user_id = users.user_id
          ORDER BY users.name";

 

Here is a few tutorials/articles on SQL Joins

http://www.w3schools.com/sql/sql_join.asp

http://en.wikipedia.org/wiki/Join_(SQL)

 

And some helpful links posted by fenway can be found here

http://www.phpfreaks.com/forums/index.php/topic,125105.0.html