display information from a database to a webpage

[pete2000]

ok, here comes another bug in my code 😃

what i am trying to do is display the home teams and away teams from the data in my database to be displayed on a web page

here is the code i am using

function viewFixtureList($table, $table1, $where){
/* function to view a table in a html table, with column headings 
$where is an optional SQL where clause used to limit the rows selected*/

global $DBHOST, $DBUSER,$DBPASSWORD,$DBDATABASE;

/* first connect to the database */
$dbcnx = myconnect();

/* now get the column headers */
$fields = mysql_list_fields($DBDATABASE, $table, $dbcnx);

$columns = mysql_num_fields($fields);

?> <!-- create the table headings -->

	<TABLE border = 1> <TR> 
  	<TD ALIGN="center"> <em>Date</TD>
  	<TD ALIGN="center"> <em>HomeID</TD>
	<TD ALIGN="center"> <em>HomeName</TD>
  	<TD ALIGN="center"> <em>AwayID</TD>
	<TD ALIGN="center"> <em>AwayName</TD>
    </em></TR>
<?php	


/* now get the data */
$query = "SELECT matchDate, homeClubID, clubName, awayClubID, clubName  
FROM $table, $table1 $where WHERE $table.homeClubID = $table1.clubID AND matchDate = '2003-08-09'";
	if (! mysql_query($query))
        printf ("Error: %s<br>%s<br>", mysql_error (), $query);
		$result = mysql_query($query);

/* and work through the data row by row creating a line in the table */
$i = 0;  
while ($i < mysql_num_rows ($result)) {  
    $row = mysql_fetch_array($result); 
    echo "<tr>\n";  
    for ($j = 0; $j < $columns; $j++) { 
         echo "<td> {$row[$j]}</td>\n"; 
     };  
     echo "</tr>\n";  
    $i++;  
}; 

	/* now close the table */
?> </table> <?php
};

what is happening is that i can only display the home teams or away teams but not both. thank you for you help 🙂

[sneakyimp]

I'm not entirely sure but i think it's your SQL query that needs work. I don't have a lot of experience querying from more than one table at a time, but i think the problem is that you refer to clubName twice...meaning different things each time.

Here's your query:

$query = "SELECT matchDate, homeClubID, clubName, awayClubID, clubName   

    FROM $table, $table1 $where WHERE $table.homeClubID = $table1.clubID AND matchDate = '2003-08-09'"

Maybe try this:

$query = "SELECT matchDate, homeClubID, $table.clubName, awayClubID, $table1.clubName   

    FROM $table, $table1 $where WHERE $table.homeClubID = $table1.clubID AND matchDate = '2003-08-09'"

If that doesn't work, think about downloading phpMyAdmin...it's an incredible PHP project that lets you browse your dbase, construct queries, make changes, etc. truly awesome.

[pete2000]

hey sneakyimp, thank you for helping me but when i ran the query you gave me, i got a parse error, so i don't think it liked it. back to the drawing board i go🙁

[sneakyimp]

like i said, i'm not all that good at queries. 🙁

and as i look again, i maybe had it screwed up. what are the actual names of your tables? and what fields (columns) are in each table? that would be helpful. as best i can tell, 'table' has columns homeClubID and matchDate. which column has awayClubID? how about clubName? it would be helpful to know that stuff.

[pete2000]

the names of my tables are in question are

<?php viewFixtureList($table_Fixtures, $table_club, ""); ?>

function viewFixtureList($table, $table1, $where){
/* function to view a table in a html table, with column headings 
$where is an optional SQL where clause used to limit the rows selected*/

global $DBHOST, $DBUSER,$DBPASSWORD,$DBDATABASE;

/* first connect to the database */
$dbcnx = myconnect();

/* now get the column headers */
$fields = mysql_list_fields($DBDATABASE, $table, $dbcnx);

$columns = mysql_num_fields($fields);

?> <!-- create the table headings -->

	<TABLE border = 1> <TR> 
  	<TD ALIGN="center"> <em>Date</TD>
  	<TD ALIGN="center"> <em>HomeID</TD>
	<TD ALIGN="center"> <em>HomeName</TD>
  	<TD ALIGN="center"> <em>AwayID</TD>
	<TD ALIGN="center"> <em>AwayName</TD>
    </em></TR>
<?php	

/* start a table to put things in then loop round and put in the headers 
?> <table border="1"> <?php
for ($j = 0; $j < $columns; $j++) { ?>
  		<td> <?php echo(mysql_field_name($fields, $j)); ?> </td>
	<td> <?php };
?> <tr> <?php*/

/* now get the data */
$query = "SELECT matchDate, homeClubID, clubName, awayClubID, clubName  
FROM $table, $table1 $where WHERE $table.homeClubID = $table1.clubID AND matchDate = '2003-08-09'";
	if (! mysql_query($query))
        printf ("Error: %s<br>%s<br>", mysql_error (), $query);
		$result = mysql_query($query);

/* and work through the data row by row creating a line in the table */
$i = 0;  
while ($i < mysql_num_rows ($result)) {  
    $row = mysql_fetch_array($result); 
    echo "<tr>\n";  
    for ($j = 0; $j < $columns; $j++) { 
         echo "<td> {$row[$j]}</td>\n"; 
     };  
     echo "</tr>\n";  
    $i++;  
}; 



/* and work through the data row by row creating a line in the table 
$i = 0;
while ($i < mysql_num_rows ($result)) {
	$row = mysql_fetch_array($result);
	for ($j = 0; $j < $columns; $j++) { ?>
      	<td> <?php echo($row[mysql_field_name($fields, $j)]); ?> </td>
 		<td> <?php
 	};
 	?> <tr>  <?php
    $i++;
};*/

	/* now close the table */
?> </table> <?php
};

hope this answers your questions, thank you for your help 🙂

[sneakyimp]

i still don't know what the actual tables are in the database or their structure. you gave me this:

<?php viewFixtureList($table_Fixtures, $table_club, ""); ?> 

I guess I don't necessarily need to know what the exact values are for $table_fixtures and $table_club, but i do need to know their structure....that is to say what fields exist in the actual SQL database in each of these two tables. without this info, i don't think I can provide much help.

[pete2000]

i got you now, here are club columns

clubID
clubName

and also the fixture columns

matchDate
matchID
homeClubID
awayClubID

hope this helps alittle better,
thanks again 🙂

[sneakyimp]

OK...i think i got it here. the trick to this is that you were referring to the same table twice in a single query, wanting different values each time. this necessitates that we use aliases (the keyword 'AS' helps us with this) and also a JOIN. I'm really bad at joins in sql, but I think this might be what you want...i haven't go the club IDs in there but it doesn't seem to me like you want them? i imagine that a club id would be '1' and not 'Fenway Park' or whatever. if i assume correctly, try this:

$sql = "SELECT f1.matchDate, s1.clubName AS homeClubName, s2.clubName AS awayClubName
FROM $table as f1 LEFT OUTER JOIN $table1 AS s1 ON f1.homeClubID=s1.clubID LEFT OUTER JOIN $table1 AS s2 ON f1.awayClubID=s2.clubID AND f1.matchDate = '2004-04-30';"; 

You might also want to clean up your query parsing code....your if statement reports an error if nothing is returned but then goes on to parse the non-existent data anyway. move that parsing into an ELSE clause. also, you run the query a few times...once is enough.

OH...if you want the club ids too, you just need to change the first part of the select statement...something like this (i haven't test it...you can 😃 ):

$sql = "SELECT f1.matchDate, f1.homeClubID, s1.clubName AS homeClubName, f1.awayClubID, s2.clubName AS awayClubName
FROM $table as f1 LEFT OUTER JOIN $table1 AS s1 ON f1.homeClubID=s1.clubID LEFT OUTER JOIN $table1 AS s2 ON f1.awayClubID=s2.clubID AND f1.matchDate = '2004-04-30';"; 

OH...by the way i changed the date...used my birthday instead just for testing.

you should check out PHPMYADMIN....easy to install PHP project that lets you do all KINDS of things on a database. that's how I figured this out.

hope this helps!

[pete2000]

thank you for your help sneakyimp, and it is 99% working, the only problem is, is that it doesn't recognise the date as it displays all the fixtures in the table and not just that date. Also, i have got phpmyadmin downloaded and i had a look, but no luck there
thaks again 🙂