[SOLVED] query confusion (and hopefully unconfusion)...

[wafflestomper]

I can get queries to work when I am only querying one table at a time.  But when I try to do a few, it seems to mess up.  Perhaps I'm not understanding how it works...

 

<?php
function display_account_prefs()
{
mysql_connect('localhost', 'username', 'passwd') or die('Could not connect: ' . mysql_error());
mysql_select_db('dbname') or die('Could not select database');

$username = "casey";
$query = "select * from students, grade, school, teacher where username = $username";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row = $result->fetch_assoc();

echo $row['fname'];
echo $row['lname'];
echo $row['school_id']
}
?>

 

Here are the tables:

Student Table

Teacher Table

Grade Table

SchoolTable

 

When I run this query, I get an error:

Query failed: Unknown column 'casey' in 'where clause'

 

What am I doing wrong?

 

or maybe a better question, how do I join all the tables to then use all the information?  I want to be able to select all of the information for "casey" in the student table which should give me her first name, last name, school id, teacher id, and grade id.  I want it to then also pull the names of the teachers, grades, and school from the other tables so that the names of those items can be displayed, not just the id numbers.  How do I do that?

[Barand]

Firstly, a string value that isn't inside quotes is assumed to be a column name so you need quotes round $username.

 

$query = "select * from students, grade, school, teacher where username = '$username' ";

 

 

 

Secondly, "SELECT * FROM students, grade, school, teacher" without any join conditions  on the tables will join every record in each table with every record in the other tables, so in your case you will get 4 x 6 x 12 x 4  (= 1,152) records returned instead of the one you want.

 

try

SELECT s.fname, s.lname, sc.school_name, t.teacher_name, g.grade_name
FROM student s
INNER JOIN school sc ON s.school_id = sc.school_id
INNER JOIN teacher t ON t.teacher_id = s.teacher_id
INNER JOIN grade g ON s.grade_id = g.grade_id
WHERE s.username = '$username'

[anthylon]

SELECT students.*, school.* FROM students 
LEFT JOIN school ON students.student_id = school.school_id

 

This would work for two tables. I'm tired and in my country is almost 3AM (morning). The query you need would be more complex but based on previous example. You can see more about JOIN on the internet because that is important to understand (concept). After you understand it you will be able to make your own queries.

 

Best wishes 

 

[anthylon]

Well Barand was faster...

 

His answer is perfect. Thanks 

[Barand]

It's only 2am here

[wafflestomper]

Thanks.  That INNER JOIN will be very helpful.  It made a lot of sense the way you put it.  My problem is that is still doesn't work.  It's probably something I had done earlier...  Anyone help? 

 

<?php
function db_connect()
{
   $result = new mysqli('localhost', 'username', 'passwd', 'db'); 
   if (!$result)
     throw new Exception('Could not connect to database server');
   else
     return $result;
}

function display_account_prefs()
{

	$conn = db_connect();
	$username = $_SESSION['valid_user'];
$query = "SELECT s.fname, s.lname, sc.school_name, t.teacher_name, g.grade_name
FROM student s
INNER JOIN school sc ON s.school_id = sc.school_id
INNER JOIN teacher t ON t.teacher_id = s.teacher_id
INNER JOIN grade g ON s.grade_id = g.grade_id
WHERE s.username = '$username'"; 
$result = $conn->query($query);
$row = $result->fetch_assoc();  //<--- THIS IS THE ERROR LINE

echo $row['student.fname'];
echo $row['student.lname'];
echo $row['school.school_name'];
echo $row['teacher.teacher_name'];
echo $row['grade.grade_name'];
?>

 

I get the following FATAL ERROR:

 

Fatal error: Call to a member function fetch_assoc() on a non-object in /home/wepcorg/public_html/zollinhofer/functions.php on line 149 (the one in red)

 

 

[anthylon]

That function on error line should be: mysql_fetch_assoc($result).

 

Here is code from PHP manual that works:

 

<?
$conn = mysql_connect("localhost", "mysql_user", "mysql_password");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

$sql = "SELECT s.fname, s.lname, sc.school_name, t.teacher_name, g.grade_name
FROM student s
INNER JOIN school sc ON s.school_id = sc.school_id
INNER JOIN teacher t ON t.teacher_id = s.teacher_id
INNER JOIN grade g ON s.grade_id = g.grade_id
WHERE s.username = '$username'"; 

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
echo $row['fname'];
echo $row['lname'];
echo $row['school_name'];
echo $row['teacher_name'];
echo $row['grade_name'];

}

mysql_free_result($result);
?>

 

Also I think your INNER will not work. I'm really tired so I can't check it well but I think it will not work. You should use LEFT or RIGH join. You will get some rows more than once (the same) because of that INNER. But, test it...

[Barand]

try check for an error when you call the query. Also omit tablename prefixes when echoing results of a query.

 

<?php
	$conn = db_connect();
	$username = $_SESSION['valid_user'];
$query = "SELECT s.fname, s.lname, sc.school_name, t.teacher_name, g.grade_name
FROM student s
INNER JOIN school sc ON s.school_id = sc.school_id
INNER JOIN teacher t ON t.teacher_id = s.teacher_id
INNER JOIN grade g ON s.grade_id = g.grade_id
WHERE s.username = '$username'"; 
$result = $conn->query($query);
if (!$result) {
    echo $conn->error();
}
else {
    $row = $result->fetch_assoc();  //<--- THIS IS THE ERROR LINE
    echo $row['fname'];
    echo $row['lname'];
    echo $row['school_name'];
    echo $row['teacher_name'];
    echo $row['grade_name'];
}
?>

[wafflestomper]

I found this little bit of code somewhere that helped me figure out what the error actually was.

[code
<?php
if ($row = mysql_fetch_row($result)) {
           return $row;
        } else {
           print (mysql_error());
        }
    break;;
?>