Jump to content

MySQL Seleect Statement

tgreene

By tgreene
in PHP Coding Help

 Share

Recommended Posts

tgreene Newbie

tgreene

Posted
Posted

I'm looking for the proper php select statement that will omit blank rows.

Here is a current snip of what I'm dealing with:

 

print "<font size=\"-1\">\n ";
print "<table width=\"100%\" border=\"1\" >\n";
$first_row = "<tr>\n<td> </td>\n<td> </td>\n<td> </td>\n";
$second_row = "<tr>\n<td><font size=\"-2\">USERS</td>\n<td><font size=\"-2\">Service Unit</td>\n<td><font size=\"-2\">phone number</td>\n";
print "<p>User Administration and Tracking</p>\n";
if($course == 0)
{
	$sql = "SELECT * FROM course";
} else
{
	$sql = "SELECT * FROM course where course_num=\"$course\"";
}
$result = mysql_query($sql);
if (!($result))
{
	print "SELECT Error <br>\n";
	 print (mysql_error() . "<br>\n");
	exit();
}
while ($courses = mysql_fetch_array($result))
{
	$coursecol = 0;
	$sql2 = "SELECT * FROM quizes WHERE course_num = $courses[course_num] ORDER by orderval";
	$result2 = mysql_query($sql2);
	if (!($result2))
	{
		print "SELECT Error <br>\n";
		 print (mysql_error() . "<br>\n");
		exit();
	}
	while ($quizes = mysql_fetch_array($result2))
	{
		$colcount ++;
		$coursecol ++;
		$course[$colcount]= $courses[course_num];
		$section[$colcount]= $quizes[quiz_num];
		$second_row .= "<td><font size=\"-2\">$quizes[description]</td>\n";
	}
	$first_row .= "<td colspan=\"$coursecol\">$courses[description]</td>\n";
}
print "$first_row\n</tr>\n";
print "$second_row\n</tr>\n";
if ($serviceunit == 0)
{
	if ($sort == "serviceunit")
	{
		$sql = "SELECT * FROM main order by service_unit ";
	}else
	{
		$sql = "SELECT * FROM main order by last_name";
	}
} else
{
	$sql = "SELECT * FROM main where service_unit = \"$serviceunit\" order by last_name";

 

It then prints it out in a nice table. I'm looking for a way to not print anything that has blank rows. It will however have data only from the last_name column.

I'm not sure if this is enough information.. I'll provide more if needed  ;D

Link to comment
https://forums.phpfreaks.com/topic/50507-mysql-seleect-statement/
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

Do not use looping queries! It is extreemely inefficient. That is also the source of your problem.

 

Instead of querying for courses and then querying for quizes, etc, etc. You can get all the data you need (without blank rows) in one query!

 

Give me a few minutes to look at your code to see if I can come up with something.

Psycho Prolific Member

Psycho

Posted
Posted

OK, this should do the same thing you have above in a much more efficient manner. However, I suspect you will still have the same display problem. I'm not sure what "blank" rows you are getting - in fact I don't understand how you are trying to display these records. I appears you are creating a table with the course and quiz records going from left to right. Typically records are displayed top to bottom.

 

If you could provide an example of how you want the records displayed I can assis further.

 

Note: there may be some typos

print "<font size=\"-1\">\n ";
print "<table width=\"100%\" border=\"1\" >\n";

$first_row = "<tr>\n<td> </td>\n<td> </td>\n<td> </td>\n";
$second_row = "<tr>\n<td><font size=\"-2\">USERS</td>\n<td><font size=\"-2\">Service Unit</td>\n<td><font size=\"-2\">phone number</td>\n";
print "<p>User Administration and Tracking</p>\n";


$sql = "SELECT c.course_num, c.description as course_desc,
               q.quiz_num, q.description as quiz_descr
        FROM course c
        LEFT JOIN quizes q ON c.course_num = q.course_num\n ";

if($course)
{
    $sql .= "WHERE c.course_num='$course'\n";
}

$sql .= "ORDER BY q.orderval";

$result = mysql_query($sql);

if (!($result))
{
    print "SELECT Error <br>\n";
    print (mysql_error() . "<br>\n");
    exit();
}

while ($row = mysql_fetch_array($result)) {

    $colcount ++;
    $coursecol ++;
    $course[$colcount]= $row[course_num];
    $section[$colcount]= $row[quiz_num];
    $second_row .= "<td><font size=\"-2\">$row[quiz_descr]</td>\n";

    $first_row .= "<td colspan=\"$coursecol\">$row[course_desc]</td>\n";
}

print "$first_row\n</tr>\n";
print "$second_row\n</tr>\n";

if ($serviceunit == 0)
{
if ($sort == "serviceunit")
{
	$sql = "SELECT * FROM main order by service_unit ";
}else
{
	$sql = "SELECT * FROM main order by last_name";
}
} else
{
$sql = "SELECT * FROM main where service_unit = \"$serviceunit\" order by last_name";
}

Psycho Prolific Member

Psycho

Posted
Posted

OK, so if I am understanding this the queries against courses and quizes make up the first two rows and the query against main mukes up the additional rows. That makes no sense to me. How do you know that a particular column in main will line up with a particular column from quizes? You don't seem to tie those table together in any way.

 

In any event you did not post the full code that you are using to display the rows of the individual user data. To be honest, I'm really not understanding the structure of your data. Can you provide the structure of the tables in question?

tgreene Newbie

tgreene

Posted
  • Author
Posted 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>E-learning Reports</title>
</head>

<body>
<?php
mysql_connect("localhost","xxx","xxx") 
or die ("Unable to connect to server.");
// select database on MySQL server
mysql_select_db("manitou")
or die ("Unable to connect to database.");
if($getreport)
{
$startstamp = mktime(0,0,0,$start_mon,$start_day,$start_year);
$endstamp = mktime(0,0,0,$end_mon,$end_day,$end_year);
$colcount = 0;
print "<font size=\"-1\">\n ";
print "<table width=\"100%\" border=\"1\" >\n";
$first_row = "<tr>\n<td> </td>\n<td> </td>\n<td> </td>\n";
$second_row = "<tr>\n<td><font size=\"-2\">USERS</td>\n<td><font size=\"-2\">Service Unit</td>\n<td><font size=\"-2\">phone number</td>\n";
print "<p>User Administration and Tracking</p>\n";
if($course == 0)
{
	$sql = "SELECT * FROM course";
} else
{
	$sql = "SELECT * FROM course where course_num=\"$course\"";
}
$result = mysql_query($sql);
if (!($result))
{
	print "SELECT Error <br>\n";
	 print (mysql_error() . "<br>\n");
	exit();
}
while ($courses = mysql_fetch_array($result))
{
	$coursecol = 0;
	$sql2 = "SELECT * FROM quizes WHERE course_num = $courses[course_num] ORDER by orderval";
	$result2 = mysql_query($sql2);
	if (!($result2))
	{
		print "SELECT Error <br>\n";
		 print (mysql_error() . "<br>\n");
		exit();
	}
	while ($quizes = mysql_fetch_array($result2))
	{
		$colcount ++;
		$coursecol ++;
		$course[$colcount]= $courses[course_num];
		$section[$colcount]= $quizes[quiz_num];
		$second_row .= "<td><font size=\"-2\">$quizes[description]</td>\n";
	}
	$first_row .= "<td colspan=\"$coursecol\">$courses[description]</td>\n";
}
print "$first_row\n</tr>\n";
print "$second_row\n</tr>\n";
if ($serviceunit == 0)
{
	if ($sort == "serviceunit")
	{
		$sql = "SELECT * FROM main order by service_unit ";
	}else
	{
		$sql = "SELECT * FROM main order by last_name";
	}
} else
{
	$sql = "SELECT * FROM main where service_unit = \"$serviceunit\" order by last_name";
}
$result = mysql_query($sql);
if (!($result))
{
	print "Select Error <br>\n";
	 print (mysql_error() . "<br>\n");
	exit();
}

while ($users = mysql_fetch_array($result))
{
	print "<tr>\n";
	print "<td><font size=\"-2\"><a href=\"index.php?level=user&individule=$users[id_num]\">$users[first_name] $users[last_name]</a></td>\n<td><font size=\"-2\">$users[service_unit]</td>\n<td><font size=\"-2\">$users[phone]</td>\n";
	for ($i=1;$i<=$colcount;$i++)
	{
		$sql2 = "SELECT * FROM progress WHERE id_num = $users[id_num] and quiz_num = $section[$i] and timestamp > $startstamp and timestamp < $endstamp";
		$result2 = mysql_query($sql2);
		if (!($result2))
		{
			print "SELECT2 Error <br>\n";
			print "$sql2 <br/>\n";
			 print (mysql_error() . "<br>\n");
			exit();
		}
		if($complete = mysql_fetch_array($result2))
		{
			$daycomplete = date("m/d/Y",$complete[timestamp]);
			print "<td><font size=\"-2\">$daycomplete</td>\n";
		} else
		{
			print"<td> </td>\n";
		} 
	}
	print "</td>\n";
}
print "</table>\n";
print "</font>\n";
print "<p align=\"left\"><a href=\"$PHP_SELF\">New Report</a></p>\n";
print "<p align=\"left\"><a href=\"index.php\">Back to Admin page</a></p>\n";
} else
{
$this_mon=date("m", mktime());
$this_day=date("d", mktime());
$this_year=date("Y", mktime());
?>
<form name="ReportForm" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
Service Unit: 
<?php
print "<select name=\"serviceunit\" size=\"1\">";
print" <option value=\"0\" selected > ALL</option>";
$sql = "SELECT * FROM Service_Unit";
$result = mysql_query($sql);
	if (!($result))
	{
		print "SELECT Error <br>\n";
		print "<p> $sql </p>\n";
		print (mysql_error() . "<br>\n"); 
		exit();
	}
while ($service = mysql_fetch_array($result)) 
{

	print" <option value=\"$service[report_code]\"> $service[service_unit]</option>";

}
  print "</select><br/>\n";
?>
<p>Course 
<?php
print "<select name=\"course\" size=\"1\">";
print" <option value=\"0\" selected > ALL</option>";
$sql = "SELECT * FROM course";
$result = mysql_query($sql);
	if (!($result))
	{
		print "SELECT Error <br>\n";
		print "<p> $sql </p>\n";
		print (mysql_error() . "<br>\n"); 
		exit();
	}
while ($course = mysql_fetch_array($result)) 
{
	print" <option value=\"$course[course_num]\"> $course[description]</option>";
}
  print "</select><br/>\n";
  ?>
  </p>
  <table>
  <tr>
  <td rowspan="2" valign="bottom">Start Date:</td>
  <td>
  Month
  </td>
  <td>
  Day
  </td>
  <td>
  Year
  </td>
  </tr>
	<tr>
  <td align="right">
  <input type="text" size="4" name="start_mon" value="01">
  </td>
 <td>
  <input type="text" size="4" name="start_day" value="01">
  </td>
  <td>
  <input type="text" size="6" name="start_year" value="2004">
  </td>
  </tr>
  </table>
  <table>
  <tr>
  <td rowspan="2" valign="bottom">End Date:</td>
  <td>
  Month
  </td>
  <td>
  Day
  </td>
  <td>
  Year
  </td>
  </tr>
	<tr>
  <td align="right">
  <input type="text" size="4" name="end_mon" value="<?php echo $this_mon ?>">
  </td>
 <td>
  <input type="text" size="4" name="end_day" value="<?php echo $this_day ?>">
  </td>
  <td>
  <input type="text" size="6" name="end_year" value="<?php echo $this_year ?>">
  </td>
  </tr>
  </table>
  <br>
  <input type="submit" name="getreport" value="Submit">
</form>
<p align="left"><a href="index.php">Back to Admin page</a></p>
<?php
}
?>
</body>
</html>

tgreene Newbie

tgreene

Posted
  • Author
Posted

database

  |---

      |----quizes

            |---course_num

            |---quiz_num

            |---description

            |---questions

            |---intro

            |---orderval

      |----course

            |---course_num

            |---description

            |---intro

            |---order

Psycho Prolific Member

Psycho

Posted
Posted

Ok, after reviewing your last two posts I think what you are looking to display is something like this:

 

----------------------------------------------------------------------------------
|        |           |     |         Course 1         |         Course 2         |
----------------------------------------------------------------------------------
|USERS   | SRVC CODE | PH# | QUIZ 1 | QUIZ 2 | QUIZ 3 | QUIZ 4 | QUIZ 5 | QUIZ 6 |
----------------------------------------------------------------------------------
|User 1  |   1234    | 123 | 1/1/07 |        |        |        | 4/4/07 |        |
--------------------------------------------------------------------------------
|User 2  |   1234    | 123 |        | 1/1/07 | 6/4/07 |        |        |        |
----------------------------------------------------------------------------------
|User 3  |   1234    | 123 |        |        | 5/9/07 |        |        |        |
----------------------------------------------------------------------------------

 

If that is correct, then you can do it all much easier than you are doing it now. Give me some time to work on it. Can you also show me the structure for main and progrsss as well?

tgreene Newbie

tgreene

Posted
  • Author
Posted

database

|---

  |----quizes

|---course_num

|---quiz_num

|---description

|---questions

|---intro

|---orderval

  |----course

|---course_num

|---description

|---intro

|---order

  |----main

|---id_num

|---service_unit

|---gsid

|---first_name

|---last_name

|---address

|---city

|---state

|---zip

|---phone

|---e_mail

|---administrator

|----progress

|---id_num

|---quiz_num

|---course_num

|---timestamp

 

 

 

That is what I'm looking for yes, but not a whole over haul of it. It is working, just taking out the people who are in the service unit, but may not have taken any of the quizes/courses.

Psycho Prolific Member

Psycho

Posted
Posted

Ok, your code is incredibly ineficient. You should never have looping queries. I had done something similar and was trying to clean up your code a bit, but I don't have the time to wrap my head around it. But, if you start having larger numbers of users/quizes your script will run incredibly slow. So, I give up on that. Instead I will give you a workaround.

 

Replace your loop for displaying the user data with this. Basically I changes all the print's to assigning the text to a variable and I create a switch to determine whether or not to display the row when done. See the comments in the code for more info

<?php

while ($users = mysql_fetch_array($result))
{
	//Create switch and variable for the row
	$userData = false;
	$userRow = "<tr>\n";

	$userRow .= "<td><font size=\"-2\"><a href=\"index.php?level=user&individule=$users[id_num]\">$users[first_name] $users[last_name]</a></td>\n<td><font size=\"-2\">$users[service_unit]</td>\n<td><font size=\"-2\">$users[phone]</td>\n";
	for ($i=1;$i<=$colcount;$i++)
	{
		$sql2 = "SELECT * FROM progress WHERE id_num = $users[id_num] and quiz_num = $section[$i] and timestamp > $startstamp and timestamp < $endstamp";
		$result2 = mysql_query($sql2);
		if (!($result2))
		{
			print "SELECT2 Error <br>\n";
			print "$sql2 <br/>\n";
			print (mysql_error() . "<br>\n");
			exit();
		}
		if($complete = mysql_fetch_array($result2))
		{
			// Set switch to true
			$userData = true;
			$daycomplete = date("m/d/Y",$complete[timestamp]);
			$userRow .= "<td><font size=\"-2\">$daycomplete</td>\n";
		} else
		{
			$userRow .= "<td> </td>\n";
		} 
	}
	$userRow .= "</td>\n";
	//Display row if there was any data
                if ($userData) { print $userRow; }
}

?>

tgreene Newbie

tgreene

Posted
  • Author
Posted

Ok I subbed it in but im getting a parse error in $end??

Did I miss something?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>E-learning Reports</title>
</head>

<body>
<?php
mysql_connect("localhost","xxxx","xxxxxx") 
or die ("Unable to connect to server.");
// select database on MySQL server
mysql_select_db("manitou")
or die ("Unable to connect to database.");
if($getreport)
{
$startstamp = mktime(0,0,0,$start_mon,$start_day,$start_year);
$endstamp = mktime(0,0,0,$end_mon,$end_day,$end_year);
$colcount = 0;
print "<font size=\"-1\">\n ";
print "<table width=\"100%\" border=\"1\" >\n";
$first_row = "<tr>\n<td> </td>\n<td> </td>\n<td> </td>\n";
$second_row = "<tr>\n<td><font size=\"-2\">USERS</td>\n<td><font size=\"-2\">Service Unit</td>\n<td><font size=\"-2\">phone number</td>\n";
print "<p>User Administration and Tracking</p>\n";
if($course == 0)
{
	$sql = "SELECT * FROM course";
} else
{
	$sql = "SELECT * FROM course where course_num=\"$course\"";
}
$result = mysql_query($sql);
if (!($result))
{
	print "SELECT Error <br>\n";
	 print (mysql_error() . "<br>\n");
	exit();
}
while ($courses = mysql_fetch_array($result))
{
	$coursecol = 0;
	$sql2 = "SELECT * FROM quizes WHERE course_num = $courses[course_num] ORDER by orderval";
	$result2 = mysql_query($sql2);
	if (!($result2))
	{
		print "SELECT Error <br>\n";
		 print (mysql_error() . "<br>\n");
		exit();
	}
	while ($quizes = mysql_fetch_array($result2))

while ($users = mysql_fetch_array($result))
{
	//Create switch and variable for the row
	$userData = false;
	$userRow = "<tr>\n";

	$userRow .= "<td><font size=\"-2\"><a href=\"index.php?level=user&individule=$users[id_num]\">$users[first_name] $users[last_name]</a></td>\n<td><font size=\"-2\">$users[service_unit]</td>\n<td><font size=\"-2\">$users[phone]</td>\n";
	for ($i=1;$i<=$colcount;$i++)
	{
		$sql2 = "SELECT * FROM progress WHERE id_num = $users[id_num] and quiz_num = $section[$i] and timestamp > $startstamp and timestamp < $endstamp";
		$result2 = mysql_query($sql2);
		if (!($result2))
		{
			print "SELECT2 Error <br>\n";
			print "$sql2 <br/>\n";
			print (mysql_error() . "<br>\n");
			exit();
		}
		if($complete = mysql_fetch_array($result2))
		{
			// Set switch to true
			$userData = true;
			$daycomplete = date("m/d/Y",$complete[timestamp]);
			$userRow .= "<td><font size=\"-2\">$daycomplete</td>\n";
		} else
		{
			$userRow .= "<td> </td>\n";
		} 
	}
	$userRow .= "</td>\n";
	//Display row if there was any data
                if ($userData) { print $userRow; }
}


$result = mysql_query($sql);
if (!($result))
{
	print "Select Error <br>\n";
	 print (mysql_error() . "<br>\n");
	exit();
}

while ($users = mysql_fetch_array($result))
{
	print "<tr>\n";
	print "<td><font size=\"-2\"><a href=\"index.php?level=user&individule=$users[id_num]\">$users[first_name] $users[last_name]</a></td>\n<td><font size=\"-2\">$users[service_unit]</td>\n<td><font size=\"-2\">$users[phone]</td>\n";
	for ($i=1;$i<=$colcount;$i++)
	{
		$sql2 = "SELECT * FROM progress WHERE id_num = $users[id_num] and quiz_num = $section[$i] and timestamp > $startstamp and timestamp < $endstamp";
		$result2 = mysql_query($sql2);
		if (!($result2))
		{
			print "SELECT2 Error <br>\n";
			print "$sql2 <br/>\n";
			 print (mysql_error() . "<br>\n");
			exit();
		}
		if($complete = mysql_fetch_array($result2))
		{
			$daycomplete = date("m/d/Y",$complete[timestamp]);
			print "<td><font size=\"-2\">$daycomplete</td>\n";
		} else
		{
			print"<td> </td>\n";
		} 
	}
	print "</td>\n";
}
print "</table>\n";
print "</font>\n";
print "<p align=\"left\"><a href=\"$PHP_SELF\">New Report</a></p>\n";
print "<p align=\"left\"><a href=\"index.php\">Back to Admin page</a></p>\n";
}
{
$this_mon=date("m", mktime());
$this_day=date("d", mktime());
$this_year=date("Y", mktime());
?>
<form name="ReportForm" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
Service Unit: 
<?php
print "<select name=\"serviceunit\" size=\"1\">";
print" <option value=\"0\" selected > ALL</option>";
$sql = "SELECT * FROM Service_Unit";
$result = mysql_query($sql);
	if (!($result))
	{
		print "SELECT Error <br>\n";
		print "<p> $sql </p>\n";
		print (mysql_error() . "<br>\n"); 
		exit();
	}
while ($service = mysql_fetch_array($result)) 
{

	print" <option value=\"$service[report_code]\"> $service[service_unit]</option>";

}
  print "</select><br/>\n";
?>
<p>Course 
<?php
print "<select name=\"course\" size=\"1\">";
print" <option value=\"0\" selected > ALL</option>";
$sql = "SELECT * FROM course";
$result = mysql_query($sql);
	if (!($result))
	{
		print "SELECT Error <br>\n";
		print "<p> $sql </p>\n";
		print (mysql_error() . "<br>\n"); 
		exit();
	}
while ($course = mysql_fetch_array($result)) 
{
	print" <option value=\"$course[course_num]\"> $course[description]</option>";
}
  print "</select><br/>\n";
  ?>
  </p>
  <table>
  <tr>
  <td rowspan="2" valign="bottom">Start Date:</td>
  <td>
  Month
  </td>
  <td>
  Day
  </td>
  <td>
  Year
  </td>
  </tr>
	<tr>
  <td align="right">
  <input type="text" size="4" name="start_mon" value="01">
  </td>
 <td>
  <input type="text" size="4" name="start_day" value="01">
  </td>
  <td>
  <input type="text" size="6" name="start_year" value="2004">
  </td>
  </tr>
  </table>
  <table>
  <tr>
  <td rowspan="2" valign="bottom">End Date:</td>
  <td>
  Month
  </td>
  <td>
  Day
  </td>
  <td>
  Year
  </td>
  </tr>
	<tr>
  <td align="right">
  <input type="text" size="4" name="end_mon" value="<?php echo $this_mon ?>">
  </td>
 <td>
  <input type="text" size="4" name="end_day" value="<?php echo $this_day ?>">
  </td>
  <td>
  <input type="text" size="6" name="end_year" value="<?php echo $this_year ?>">
  </td>
  </tr>
  </table>
  <br>
  <input type="submit" name="getreport" value="Submit">
</form>
<p align="left"><a href="index.php">Back to Admin page</a></p>
<?php
}
?>
</body>
</html>

Psycho Prolific Member

Psycho

Posted
Posted

I'm seeing a few possible problems

 

Line 40: I don't see a closing bracket for this while loop.

 

Line 51: There are no brackets to specify what is included in the while loop.

 

Lines 126-127: You have a closing brackets immediately followed by an opening bracket. What is the second bracketed section supposed to be?

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.