Page 1 of 1

can't figure this out

Posted: Tue Jan 03, 2006 2:37 am
by stuffradio
I am trying to make a code that displays items from the database that is linked from one table to the other. My schema is:

Table: categories
structure:
cid int(10)
name varchar(20)

Table:station
structure:
sid int(12)
cid int(12)
mname varchar(25)
sname varchar(30)

code for station.php

Code: Select all

<?php
include("config.php");
$mname= $_POST['mname'];
$cat = mysql_query("SELECT * FROM categories");
$category = mysql_fetch_array($cat);

	echo "<table width=400 height=75 border=1 bordercolor=black cellpadding=0 cellspacing=0>
	<tr><td><center>Station Name</td><td><center>Mercora Name</td></tr>";
if ($_GET['$c'] == $category[cid]) {
$stat = mysql_query("SELECT * FROM station WHERE $category[cid]='$cid'");
while($station = mysql_fetch_array($stat)) {
extract($station);
	echo ("<tr><td><center><a href=profile.php?view=$sid>$sname</a></td><td><center>$mname</td></tr>");
}
}
echo "</table>";
exit;
?>
To see the webpage go to http://www.stufftorrents.be/mradio . Hope you can help,

Thanks

Posted: Tue Jan 03, 2006 6:42 am
by timvw

Code: Select all

SELECT cid, sid, sname, mname
FROM station
INNER JOIN categories USING cid
ORDER BY cid, sid

Code: Select all

$prev_cid = "";
while ($row = mysql_fetch_assoc($result)) {
  // test if new category is started
  if ($row['cid'] != $prev_cid) {
    // eg: ould output a </tr><tr> line to start a new table row
  }

  // display station
  // echo stuff with $row['sname'] etc...

  // update the previous cid
  $prev_cid = $row['cid'];
}

Posted: Tue Jan 03, 2006 3:10 pm
by stuffradio
This is what I have now:

Code: Select all

<?php
include("config.php");
$mname= $_POST['mname'];
$sname= $_POST['sname'];
$cat = mysql_query("SELECT cid, sid, sname, mname FROM station INNER JOIN categories USING cid ORDER BY cid, sid");
$category = mysql_fetch_array($cat);

	echo "<table width=400 height=75 border=1 bordercolor=black cellpadding=0 cellspacing=0>
	<tr><td><center>Station Name</td><td><center>Mercora Name</td></tr>";
$prev_cid = "";
$stat = mysql_query("SELECT * FROM station WHERE cid='$category[cid]'");
while($station = mysql_fetch_aassoc($stat)) {
if ($station['cid'] != $prev_cid) {
	echo ("<tr><td><center><a href=profile.php?view=$station[sid]>$station[sname]</a></td><td><center>$station[mname]</td></tr>");
}
$prev_cid = $station['cid'];
}
echo "</table>";
exit;
?>
This is what shows up on the website:

Code: Select all

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/carl/public_html/mradio/station.php on line 6
Station Name Mercora Name 

Fatal error: Call to undefined function: mysql_fetch_aassoc() in /home/carl/public_html/mradio/station.php on line 12

Posted: Tue Jan 03, 2006 4:58 pm
by pickle
Well, it's obvious that the fatal error is because you spelled mysql_fetch_assoc()wrong. Simple mistake.

The other message is telling you $cat is not a valid resource - likely because of something wrong with your query. Copy and paste your query into the command line and see what kind of error MySQL gives you.

Also, using [syntax=php][/syntax] tags colourizes your PHP code a lot nicer that the [syntax=php][/syntax] tags.

Posted: Wed Jan 04, 2006 9:34 pm
by stuffradio
This is what mysql says when I copy the query in:

Code: Select all

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_query("SELECT cid, sid, sname, mname FROM station INNER J
Query:

Code: Select all

mysql_query("SELECT cid, sid, sname, mname FROM station INNER JOIN categories USING cid ORDER BY cid, sid");

Posted: Thu Jan 05, 2006 6:51 am
by sheila
Looks like you to sent PHP code to MySQL. The query is just this part

Code: Select all

SELECT cid, sid, sname, mname FROM station INNER JOIN categories USING cid ORDER BY cid, sid
What does it say about that?

Posted: Thu Jan 05, 2006 7:23 am
by timvw
needs to be:

LEFT INNER JOIN x USING ( y)