cant get while loop to print out query data [Archive]

View Full Version : cant get while loop to print out query data

scuzzo84

02-01-2006, 06:38 AM

My site is , pick a guy from the drop down menu, and submit, and see all his skate sponsors.

I have 2 pages, the search form page and the php page that processes it.

My database is set up like this:

p[id,fname,lname]
c[id,company]
s[pid,cid,start,end]

here is the search page

<html>
<body>
<h2>Adding a new sponsor for a
<FORM action="search.php" method="POST">
<table border="1">
<tr>
<td>Name</td>
<td>
<?php
// Updating the database
// by Haroon Khalid and Barry Ambrose
// date Jan.30.06

// Connecting to database
include 'connect.php';

// Select fields from the person table to populate drop down menu
$result = mysql_query("SELECT * FROM p");

// Generating drop down menu from previous sql query
// by Barry Ambrose
$select_option = "<select name=\"ID\">";
while ($row = mysql_fetch_array($result)){
$select_option .= "<option value=\"".$row['id']."\">".$row['fname']." ".$row['lname']."</option>\n";
}
$select_option .= "</select>";
echo $select_option;
?>
</td>
<tr>
<td colspan="2" align="right">
<input type="submit">
</td>
</FORM>
</body>
</html>
The processing page

<?php
// Searching the database
// by Haroon Khalid and Brett Patterson
// date Jan/2006

// Connecting to the database
include 'connect.php';

// Var for perons ID from the search form
$ID = $_POST['ID'];

// Query for persons info based off ID
$query = "SELECT s.start, s.end, CONCAT(p.fname, ' ', p.lname ) AS p_name, c.company
FROM s
INNER JOIN p ON s.pid = p.id
INNER JOIN c ON s.cid = c.id
WHERE p.id = '".$_POST['ID']."'
ORDER BY s.start ASC";

$result = mysql_query($query);

// Use this for record check
$rows = mysql_num_rows($result);
$person = mysql_result($result, 0, 'p_name');
$company = mysql_result($result, 0, 'company');
$start = mysql_result($result, 0, 'start');
$end = mysql_result($result, 0, 'end');

// Printing out data
if ($rows < 1){
echo "NO RECORDS FOUND!";
}else{

// This stuff prints out but while loops doesnt do anything!
echo "$person ";
echo "$company ";
echo "$start ";
echo "$end ";
echo "Total: $rows records ";
echo "done ";

while($row = mysql_fetch_assoc($result))
{
echo 'Company: '.$row['company'].' Spanning: '.$row['start'].' to '.$row['end'].' ';
}
}
?>
now I want it to print like:

Bryan Herman

Company: Baker Skateboards
Spanning: 20031212 to 20051212

Company: Emerica Shoes
Spanning: 20011212 to 20021212

Now the problem is that my page does print anything. So I added this section as you can see in the processing page that makes sure the query goes thru, and it does as it does print out some guys info. This is the part that I added to make sure it actually gets data:

$person = mysql_result($result, 0, 'p_name');
$company = mysql_result($result, 0, 'company');
$start = mysql_result($result, 0, 'start');
$end = mysql_result($result, 0, 'end');

// Printing out data
if ($rows < 1){
echo "NO RECORDS FOUND!";
}else{

// This stuff prints out but while loops doesnt do anything!
echo "$person ";
echo "$company ";
echo "$start ";
echo "$end ";
echo "Total: $rows records ";
echo "done ";
But the echo loop below does nothing, I want this one to be the main part that prints out data and it doesnt. If I dont have the added code above, then all I get is a blank page.
echo 'Company: '.$row['company'].' Spanning: '.$row['start'].' to '.$row['end'].' ';
Why isnt this echo loop working?

swatisonee

02-01-2006, 09:43 AM

I'm pretty new to php but i was told that loops need to be

if {

do
{ whatever}
while
{ whatever}
else { whatever }

}

TheShaner

02-01-2006, 04:21 PM

$result = mysql_query($query);
if ($row = mysql_fetch_assoc($result))
{
echo "{$row['p_name']}\n";
do
{
echo "Company: " . $row['company'] . " \n";
echo "Spanning: " . $row['start'] . " to " . $row['end'] . "\n";
}
while($row = mysql_fetch_assoc($result));
}
else
{
echo "No records found.\n";
}
What the above code does is that it attempts to retrieve the first record. If no record is retrieved, then it outputs "No records found." If there is a record, then it outputs the person's name and then enters a loop. It prints out the person's company and span dates and then checks to see if there is another record. If so, it'll spit out the company and span dates again. This will go on until there are no more records.

-Shane

scuzzo84

02-01-2006, 05:37 PM

update: Here is a console query does this help?

mysql> SELECT s.start, s.end, CONCAT(p.fname, ' ', p.lname ) AS p_name, c.company
-> FROM s
-> INNER JOIN p ON s.pid = p.id
-> INNER JOIN c ON s.cid = c.id
-> WHERE p.id = 1
-> ORDER BY s.start ASC;
+------------+------------+----------------+------------------------+
| start | end | p_name | company |
+------------+------------+----------------+------------------------+
| 1997-12-12 | 2000-12-12 | Heath Kirchart | Foundation Skateboards |
+------------+------------+----------------+------------------------+
1 row in set (0.00 sec)

mysql>

Now I get:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/hubert/public_html/search.php on line 22

No records found.

<?php
// Searching the database
// by Haroon Khalid and Brett Patterson
// date Jan/2006

// Connecting to the database
include 'connect.php';

// Var for perons ID from the search form
$ID = $_POST['ID'];

// Query for persons info based off ID
$query = "SELECT s.start, s.end, CONCAT(p.fname, ' ', p.lname ) AS p_name, c.company
FROM s
INNER JOIN p ON s.pid = p.id
INNER JOIN c ON s.cid = c.id
WHERE p.id = ".$_POST['ID'].
"ORDER BY s.start ASC";

$result = mysql_query($query);

if ($row = mysql_fetch_assoc($result))
{
echo "{$row['p_name']}\n";
do
{
echo "Company: " . $row['company'] . " \n";
echo "Spanning: " . $row['start'] . " to " . $row['end'] . "\n";
}
while($row = mysql_fetch_assoc($result));
}
else
{
echo "No records found.\n";
}

/*
// Use this for record check
$rows = mysql_num_rows($result);
$person = mysql_result($result, 0, 'p_name');
$company = mysql_result($result, 0, 'company');
$start = mysql_result($result, 0, 'start');
$end = mysql_result($result, 0, 'end');

// Printing out data
if ($rows < 1){
echo "NO RECORDS FOUND!";
}else{

// This stuff prints out but while loops doesnt do anything!
echo "$person ";
echo "$company ";
echo "$start ";
echo "$end ";
echo "Total: $rows records ";
echo "done ";

while($row = mysql_fetch_assoc($result))
{
echo "Company: $company";
echo "Spanning: $start to $end";
}
}
*/
?>

scuzzo84

02-01-2006, 05:51 PM

resolved by esp!

" ORDER BY s.start ASC";

had to have a space! Thanks!