PHP + MySQL script problem

Limit · 12-10-2004, 02:25 PM

can someone explain why this script is not pulling out the db results

Code:

<?php
							
        require "/db/connect.php";
	$displaysql = "SELECT * FROM table";
	$displayexc = MYSQL_QUERY ($displaysql) OR DIE ("Failed to connect to database");
	$displaynum = MYSQL_NUMROWS ($displayexc);
							
	echo "<table class=\"table\" cellspacing=\"0\" cellpadding=\"4\" border=\"1\" style=\"border-collapse: collapse\" align=\"center\">
		<tr>
			<td class=\"rowtitle\">Report Time</td>
			<td class=\"rowtitle\">Name</td>
			<td class=\"rowtitle\">Title</td>
			<td class=\"rowtitle\">Status</td>
		</tr>";
								
	if ($displaynum > 0){
		FOR ($count=0;$count<$displaynum;$count++)
			{
                                $id	= MYSQL_RESULT ($displayexc,$count,'u_id');
				$date		= MYSQL_RESULT ($displayexc,$count,'date');
				$name		= MYSQL_RESULT ($displayexc,$count,'name');
				$title		= MYSQL_RESULT ($displayexc,$count,'title');
				$status		= MYSQL_RESULT ($displayexc,$count,'status');
						
	echo <<<EOF
		<tr>
		<td>
		$date
		</td>
		<td>
		$name
		</td>
		<td><a href="report.php?id=$id">$title</a>
</td>
	<td>$status</td>
		</tr>
		</table>
		EOF;
		}
		}
		else {
		echo "There are currently no entries";
		}
?>

Quote:

Parse error: parse error, unexpected $ line 43

jg_v7n · 12-10-2004, 03:23 PM

Assuming that your connect.php file is correct, try this:

I haven't tested it, but should help you in the right direction:

Code:

<?php
							
    require "/db/connect.php";
	$displaysql = "SELECT * FROM table";
	$displayexc = MYSQL_QUERY ($displaysql);
	$displaynum = MYSQL_NUMROWS ($displayexc);

?>							
	<table class="table" cellspacing="0" cellpadding="4" border="1" style="border-collapse: collapse" align="center">
		<tr>
			<td class="rowtitle">Report Time</td>
			<td class="rowtitle">Name</td>
			<td class="rowtitle">Title</td>
			<td class="rowtitle">Status</td>
		</tr>
<?php								
	
	if ($displaynum > 0)
	{
		WHILE ($displayrow = mysql_fetch_array($displayexc))
		{
               echo "	<tr>
						<td>".$displayrow['date']."</td>
						<td>".$displayrow['name']."</td>
						<td><a href='report.php?id=".$displayrow['id']."'>".$displayrow['title']."</a></td>
						<td>".$displayrow['status']."</td>
						</tr>
						</table>";
		}
	}
	else 
	{
		echo "There are currently no entries";
	}
?>

Limit · 12-10-2004, 03:31 PM

thanxs that worked can you help me out with this also.

1) When it gets the data from the database how would i sort it by date in descending order (newest entry at top)
2) When status is 'New' change the row bgcolor to #FFDE84, when the status is 'Verified' or 'In' change the row bgcolor to #FF0000 and when the status is 'Ended' change the bgcolor to #D5D5D5.
3) How can i add pages at the bottom so it only shows 25 entries a page

Limit · 12-11-2004, 02:43 AM

Limit · 12-11-2004, 03:26 AM

Hi again i have done the sort by date one i just need help with these:-

2) When status is 'New' change the row bgcolor to #FFDE84, when the status is 'Verified' or 'In' change the row bgcolor to #FF0000 and when the status is 'Ended' change the bgcolor to #D5D5D5.

3) How can i add pages at the bottom so it only shows 25 entries a page

digitman · 12-11-2004, 07:28 AM

after you retreive status from the database use something like the following code:

Code:

//Assuming $status contains the status:

if ($status=="New") {
$bgcolor="#FFDE84";
} elseif ($status=="Verified" || $status="In") {
$bgcolor="#FF0000";
} elseif ($status=="Ended") {
$bgcolor="#D5D5D5";
}

echo "<tr bgcolor=$bgcolor>";

My apologies if there are any parse errors in this code

Limit · 12-11-2004, 09:34 AM

that doesn't work it just stays the verified colour

Limit · 12-11-2004, 10:32 AM

ignore i fixed it.

Now just the page thing any ideas?

Limit · 12-11-2004, 12:52 PM

thanxs again to everyone who helped. All i need help with is this part:-

3) How can i add pages at the bottom so it only shows 25 entries a page

imaginemn · 12-11-2004, 01:18 PM

Glad you figured out your problems. I would suggest is to modify your query to the following.

SELECT date, name, id, title, status FROM table

instead of

SELECT * FROM table

If you are concerned about SPEED and OPTIMIZATION you never want to use a SELECT *. I know everyone does it and that's probably the reason many sites are typically slower and not optimized. A number 1 rule of being a DBA is to only bring back the data you need. If you are not using the additional fields don't return them. The database will run much faster and get less fragmented if you stop using SELECT *.

Just a bit of advice.

imaginemn

Limit · 12-11-2004, 01:30 PM

thanxs for the advice just changed it now and will do queries like that in the future.

Do you have any ideas how i would go about adding pages

jg_v7n · 12-13-2004, 06:29 AM

Quote:

Originally Posted by imaginemn

Glad you figured out your problems. I would suggest is to modify your query to the following.

SELECT date, name, id, title, status FROM table

instead of

SELECT * FROM table

If the only rows in the table are "date, name, id, title, status" then [select *] and [select date, name, id, title, status] will return the same data, so using [select *] would be fine in that occurance.

---

With regard to the paging - I can't write it all for you now, but here's what you need to do:

Change the Query to:

SELECT date, name, id, title, status FROM table Limit $start, 25;

Now all you need to do is set the $start variable and add some links at the bottom of your page with the anchor text "Next" and "Previous" and pass in the query string the appropriate $start value.

So for example:

Code:

  <?php
  $next_start = $start + 25;
  ?>
  
  <a href="this_page.html?start=<?php echo $next_start; ?>">Next >></a>

+ Make $start default to 0 if it is not set in the query string.
+ Make sure that you don't let $start get below 0.
+ Make sure that $start doesn't get above the maximum number of possible results.

Hope this helps...

imaginemn · 12-13-2004, 07:51 AM

Quote:

Originally Posted by jg_v7n

If the only rows in the table are "date, name, id, title, status" then [select *] and [select date, name, id, title, status] will return the same data, so using [select *] would be fine in that occurance.

jg_v7n, IMO SELECT * is just lazy coding practise. If you do SELECT * the database needs to find out what fields are actually in the table before it can then select them all. By specifying the field names the database engine can use those names straight away rather than having to do an extra lookup. Even if the query is returning the "SAME" data. It's not always about the data it's mostly about performance and the additional use of DB resources.

The sad fact is that this bad situation is not immediately apparent. In a testing environment, the programmer may have, ahem, other priorities. During volume testing, the poor performance is noticed and if your organization does not do volume testing, you may not realize that SELECT * is bad for performance until the program goes live.

imaginemn

jg_v7n · 12-13-2004, 08:30 AM

Sure it probably is lazy, I always name the rows unless I know that I want all available rows, in which case I use select * simply bacause it's quicker.

Either way I'm pretty sure that the effect is neglegable in comparison to actually returning the table data.

Thought I'd run a quick test (results inconclusive):

Code:

 <?php
 
 	function microtime_float()
 	{
 		list($usec, $sec) = explode(" ", microtime());
    		return ((float)$usec + (float)$sec);
 	} 
 	$query1 = "select * from table";
 	$query2 = "select id, date, name from table";
 	
 	mysql_connect('localhost', '');
 	mysql_select_db('table');
 	
 	$time_start = microtime_float();
 
 	mysql_query($query1);
 	
 	$time_end = microtime_float();
 	$time1 = $time_end - $time_start;
 	
 	$time_start = microtime_float();
 
 	mysql_query($query2);
 	
 	$time_end = microtime_float();
 	$time2 = $time_end - $time_start;
 	
 	echo 'Q1: '.round($time1,5);
 	echo '<br/>';
 	echo 'Q2: '.round($time2,5);
 	
 ?>

imaginemn · 12-13-2004, 08:54 AM

Nothing personal jg_v7n, but just out of curiousity how long have you been a dba?

You are correct that 1 user is NOT going to make a big difference. You will only start seeing a performance slowdown when you start having high traffic on the database. You know what, this conversation is starting to go off topic so I think I'll start my own thread to see what others think.

Microsft, Oracle, Sybase and MySQL all can't be wrong now can they?

imaginemn