CODEWITHSUNDEEP
phpmysql
ash_dev15
ash_dev15

Reputation: 45

sql works on phpmyadmin but not on php script

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?

Upvotes: 0

Views: 65

Answers (3)

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146440

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.

Upvotes: 2

chandresh_cool
chandresh_cool

Reputation: 11830

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";

Upvotes: 0

nospor
nospor

Reputation: 4220

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

Upvotes: 1

Related Questions