Getting a strange error from my query...

[Jim R]

 

Quote

Unknown column 'Carmel' in 'where clause'
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home/csi/public_html/resources/preview1920/sectional.php on line 19

 

Passing variable via the URL:

https://www.courtsideindiana.com/season-preview/19-20/sectional1920/?sectional=8&school=Carmel

Sectional = 8

School = Carmel

Before I added the &school=Carmel, it was working, just echoing the total list of schools in the table.

$sectional = $_GET['sectional'];
$school = $_GET['school'];

echo $school;

// Query

$query = "SELECT * FROM a_schools WHERE sectional=".$sectional." AND school=" . $school ."";


$results = mysqli_query($con,$query);
echo mysqli_error($con);
while($row = mysqli_fetch_assoc($results)) {

echo $row['school'] . '<br>';

}

 

[Barand]

String literals in queries need to be enclosed in single quotes. Without quotes SQL assumes they are a column name.

$query = "SELECT * FROM a_schools WHERE sectional = $sectional AND school = '$school' ";

 

[Jim R]

This is sort of combining the other topic (coaches/schools) that you’re helping me with. 

 

I have a schools table and a coach table, and I’m trying to print the school name, followed by the head coach’s name.  Right now, it’s not printing the coach’s name.  

// Getting variables out of the URL

$sectional = $_GET['sectional'];
$school = $_GET['school'];
$school_lower = strtolower($school);

// Query

$query = "SELECT * FROM a_schools as s
join a_coach as c

WHERE sectional='" . $sectional ."'  AND school='" . $school . "'
AND c.schoolID = s.ID
 ";


$results = mysqli_query($con,$query);
echo mysqli_error($con);
while($row = mysqli_fetch_assoc($results)) {

echo '<img src="/resources/preview1920/images/'. $school_lower .'/'. $school_lower .'-ad-1.png">';


echo '<div class="wrapper">';

	echo '<div class="school_info">';
	
	echo '<img src="/images/schoolLogo/'.$school.'.png">';
	echo '<div class="school_name">' . $row['school'] . ' ' . $row['nickname'] .'</div>';
	echo '<div>Head Coach: ' . $row['c.coachFirst'] . ' ' . $row['c.coachLast'] .'</div>';

Edited May 16, 2019 by Jim R

[Jim R]

This is my query now:

 

$query = "SELECT * FROM a_schools as s
join a_coach as c 
on c.schoolID = s.ID
WHERE s.sectional='" . $sectional ."'  AND s.school='" . $school . "'
 ";

 

Still not printing the coach’s name

[maxxd]

Good job - you've figured out the syntax issues with your query (I really do mean that sincerely). Now, before anything else, please look into prepared statements as you're using $_GET variables directly in a MySQL query. Then please google the performance pitfalls of running a 'SELECT * ...' query.

As for the coach name, the PHP array from a MySQL query has no idea what table each index of each row in the resultset came from, so the MySQL aliases you set up don't help the PHP output. In other words, the array indexes 'c.coachFirst' and 'c.coachLast' don't exist. The array indexes 'coachtFirst' and 'coachLast' should exist, however.

Edited May 16, 2019 by maxxd
missed a quote

[Jim R]

(I’m just using SELECT * right now because I’m just now starting this project and don’t want to have to change that line each time I add or subtract a column.) 

 

I removed the aliases in the output, and it worked, but I don’t really know why.  So thank you!

In setting up the aliases, I thought I would be told the opposite, that I wasn’t using the aliases in the query or the rest of the output.  I don’t have any repetitive column names.  

Why doesn’t the query have an idea which table each index of each row came from?

 

[Barand]

21 minutes ago, Jim R said:

Why doesn’t the query have an idea which table each index of each row came from?

The query needs to know exactly which table each column comes from. It's the output from that query to PHP that doesn't.