Using Alias in dynamic queries

PHP
1

Hello,

I have a query in my php code which is embbeded in an html page in this form:

i

nclude 'db.inc.php';
	$sql    = 'SELECT AVG(a) AS "wonder", AVG( b ) AS "mike", AVG(gabe) AS "Mazim", AVG(` Kate`) AS "Mercy"  FROM Linda';
	$result = mysql_query($sql, $link);
	if (!$result) {
    die("Query to show averages from table failed");
}
	echo "<h2><font-size = '4px'>Table: Heading</font></h2>";
	while ($row = mysql_fetch_row($result)){
	echo $row[0];
	...
        ...
	}
	mysql_free_result($result);
	mysql_close();

I want to display the result set using the Aliases and not the original column names. Please what is the acceptable thing to do given that I have both the aliases and the column names. I am little confuse here. thank you.

2

When you say these lines

mysql_free_result($result);
	mysql_close($link);

are not needed in a typical php script what do you mean? The mysql database am using has records of upto 281 thousand rows which i have to find a way to display on the webpage. If i pull these data to display dont i need to free memory and close the database using these lines? Please can you clarify? Thanks

3

That was great! Many thanks.

4

Many thanks Ryan for that short but comprehensive tutorial. I have well taken your comments.I must be comfortable with the switching.
I remain grateful.

Regards

5

thanks for that.

6

your sample is working ok but could you please show how the data can be made to be under the headings? am having issues sandwishing the tags and the php code. i admit my dumpness about this but am making progress to be independent about some fresh man’s questions thanks.

7

It is my silly mistake. I did not get the read the manual right.

I just figured out that the

mysql_close()

was empty and I had to include the resource there in like this:

mysql_close($link)

. Without this, the server crashes. Thank you guys for all the feedbacks.

8

I don’t normally have multiple dots in filenames but I don’t know if that is the problem.

I would normally save a web page with php code and with or without html code as filename.php

Try saving your file as template1.php and save ‘db.inc.php’ as db_inc.php and update your include() command.

If that doesn’t fix the problem then the problem is unfortunately over my head.

9

I have renamed the file as averages.php and removed the depricated font as you suggested but the Apache server istill encounters an error closes.

10

Yes at that point the server was not crashing. but file i was running is saved as template1.html.php is anything wrong with this?

11

Thanks Kalon, i corrected it but this time my apache server is crashing and trying find out why?

12

use double quotes for the Query but inside of query use single quotes like this:::

$sql = “SELECT AVG(no_of_satelites) AS ‘Mean Number of Satelites’, AVG( height_of_geoid ) AS ‘Mean of HDOP’, AVG(rmc_gga_longitude) AS ‘Mean of Longitude’, AVG(rmc_gga_latitude) AS ‘Mean of latitude’ FROM total_rmcs_ggas”;

13

I don’t understand what is going on here. I don’t see anything in your code that would cause your server to crash and earlier you said

I had used double quotes with the aliases without errors but the query did not execute.

which suggests that at that stage the server was not crashing.

I doubt the deprecated <font> tags you are using would crash your server.

14

save this html page(.html) as php page(.php) and try it…
If any other errors come, let me know details…

15

Sorry am here again, I had thought that with the Alias added my output will have the aliase as headings while the averages would appear just below it. The output is just showing the averages without the heading aliases. Can somebody show me the way? Now I dont get the point of using the aliases then.

16

You are right Ryan, I would try your suggestion but one thing is sure; i need to understand the tags and php combinations with regards to formating from any book.

there is something not clear to me in your sample, which position in the html page would i put this. this is confusing. the page is having so many errors with tag placement. I have attached herewith the whole portion i want to insert into the html page please look at and see show me how it should fit.

<table>
<tr>
  <th>Mean Num of Satelites</th>
  <th>Mean of HDOP</th>
  <th>Mean of Longitude</th>
  <th>Mean of latitude</th>
</tr>

<!-- Start of php code -->
	<table>
	<tr>
 	<th>Mean Num of Satelites</th>
 	<th>Mean of HDOP</th>
 	<th>Mean of Longitude</th>
  	<th>Mean of latitude</th>
    </tr>
	<?php 
	include 'db.inc.php';
	$sql    = "select AVG(no_of_satelites) as 'Mean Number of Satelites', AVG( height_of_geoid ) as'Mean of HDOP', AVG(rmc_gga_longitude) as 'Mean of Longitude', AVG(rmc_gga_latitude) as 'Mean of latitude'  FROM total_rmcs_ggas ";
	$result = mysql_query($sql, $link);
	if (!$result) {
		die("Query to show averages from table failed");
	}
    while ($row = mysql_fetch_assoc($result)){
	echo "<tr>"
             ."<td>".$row['Mean Number of Satelites']
             ."</td><td>"
             .$row['Mean of HDOP']."</td><td>"
             .$row['Mean of Longitude']
             ."</td><td>"
             .$row['Mean of latitude']
             ."</tr>";
    	}
	Print "</table>";
	
	mysql_free_result($result);
	mysql_close($link);
	
	?>	'
<!-- End of php code -->

Kindly show me how to align the table correctly so the page does not show tag errros. am confuse here.

17

Paul, i am assuming you did AVG(no_of_satelites) AS ‘Mean Number of Satelites’ to have a prettier name in your output. The AS just makes handling code easier, it doesn’t auto-change the colum.

You need to modify venkat’s code. Try:


<table>
<tr>
  <th>Mean Num of Satelites</th>
  <th>Mean of HDOP</th>
  <th>Mean of Longitude</th>
  <th>Mean of latitude</th>
</tr>
<?php
while ($row = mysql_fetch_assoc($result)){
	echo "<tr>"
             ."<td>".$row['Mean Number of Satelites']
             ."</td><td>"
             .$row['Mean of HDOP']."</td><td>"
             .$row['Mean of Longitude']
             ."</td><td>"
             .$row['Mean of latitude']
             ."</tr>";
}?>
</table>
18

please help me here, I have done this to enable me get the records under the heading but I am not right about it because there is a parse error. if you can show a sample of how to put the headings on top and the record below each heading, i would appreciate. If you dont have time then i go do some research on that.

print "<table border cellspacing= 3 cellpadding= 3><tr>";	  
    while ($info = mysql_fetch_assoc($result)){		
    print "<tr>";
	print "<th>Mean Number of Satelites</th> . <th>Mean of HDOP</th>. <th>Mean of Longitude</th> . <th>Mean of latitude</th>.</tr>";; 
	print  "<td>$info['Mean Number of Satelites']</td>" . "<td> $info['Mean of HDOP']</td>" . "<td> $info['Mean of Longitude']</td>" . "<td> $info['Mean of latitude'] </td>";
	}
	Print "</table>";
19

I just checked my apache error log and saw this message:

[Fri Oct 22 11:37:04 2010] [error] [client 127.0.0.1] File does not exist: C:/wamp/www/setapro/setapro, referer:

I dont know which file it is talking about as all the files are in the same document root directory. the two “setapro” above is the project folder and not file. hmm

20

PHP can be embeded anywhere in a page as long as you have a server with php on it, the file(s) in question have an extention of .php vs .htm/html, and the php code is between <?php ?> tags. Depending on how you code your page determines how many <?php ?> tags you can have…

So my code is a little simplier to read. it makes the heading of the table, then hits the PHP code, where I use a while loop. While loops act like


<?php 
 while(/*this is true*/){
  // do somthing
}?>

So my loop says "While PHP can find more data, going row by row in the database,
1 make a new row in my table on my webpage (<tr>),
2 make a new cell (<td>),
3 put the data named Mean Number of Satelites (etc),
4 close my cell (</td>). Repeat for each colum,
5 close that row (</tr>)
6 repeat 1-5 till php finds no more data based off the SQL query,
7 when no more data is found jump out of php
8 close table using regular HTML