sql works on phpmyadmin but not on php script

Question

I have 2 table names courses and institution. Here is my query:

CREATE TEMPORARY TABLE 
IF NOT EXISTS temp  AS (SELECT course_name, ins_name
         FROM courses 
         where course_name='ACCA') ;

select institution.ins_name, address, contact, website, 
email from institution join temp on institution.ins_name = temp.ins_name

And here is my php script:

<?php
session_start();
include('db_connect.php');
$sql="CREATE TEMPORARY TABLE 
IF NOT EXISTS temp  AS (SELECT course_name, ins_name
         FROM courses 
         where course_name='ACCA') ;

 select institution.ins_name, address, contact, website, email from institution join temp on institution.ins_name = temp.ins_name";

$result= mysqli_query($conn, $sql) or die("MySQL error: " . mysqli_error($conn)); 

while ($row = mysqli_fetch_array($result)){

echo "<tr>";
echo "<td>" . $row['ins_name'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['contact'] . "</td>";
echo "<td>" . $row['website'] . "</td>";
echo "<td>" . $row['email'] . "</td>";

echo "</tr>";
}
echo "</table>"

?>

I can't figure out what the problem is. The error i get is:

MySQL error: 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 'select institution.ins_name, address, contact, website, email from institution j' at line 6

What am i doing wrong?

Álvaro González · Accepted Answer · 2016-05-31 11:05:00Z

2

From Multiple Statements:

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

Alternatively, just fire two queries.

answered May 31, 2016 at 11:05

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nospor · Accepted Answer · 2016-05-31 11:03:10Z

1

Every query must be executed separately. create and select are two different queries so first you need to call mysqli_query for create and then mysqli_query for select

answered May 31, 2016 at 11:03

nospor

4,2301 gold badge18 silver badges26 bronze badges

1 Comment

ash_dev15 Over a year ago

got it. had to query them separately. Thank you sir

chandresh_cool · Accepted Answer · 2016-05-31 11:03:47Z

0

You need alias on each column :

 select institution.ins_name, institution.address, institution.contact, institution.website, institution.email from institution join temp on institution.ins_name = temp.ins_name";

answered May 31, 2016 at 11:03

chandresh_cool

11.9k3 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

sql works on phpmyadmin but not on php script

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related