Benafsha Arya Benafsha Arya - 18 days ago 7
PHP Question

How can i change this select sql statement into another select sql?

I have code the below code works fine but i want to have one sql statement instead of few in this code so when i try to change into one i get an error.

$eventID = $_GET['id'];

$sql = "SELECT * FROM te_events where eventID='$eventID'";
$result = $conn->query($sql);

while($row = $result->fetch_assoc())
{
$eventTitle = $row['eventTitle'];
$eventDescription = $row['eventDescription'];
$eventStartDate = $row['eventStartDate'];
$eventEndDate = $row['eventEndDate'];
$eventPrice = $row['eventPrice'];
$venueID = $row['venueID'];
$catID = $row['catID'];

$sql2 = "SELECT * FROM te_venue where venueID='$venueID'";
$result2 = $conn->query($sql2);

while($row2 = $result2->fetch_assoc())
{
$venueName = $row2['venueName'];
}
$sql3 = "SELECT * FROM te_category where catID='$catID'";
$result3 = $conn->query($sql3);

while($row3 = $result3->fetch_assoc())
{
$catName = $row3['catDesc'];
}
}
?>


I changed the code into this but it seems it is not working.

<?php
$eventID = $_GET['id'];

$sql = "SELECT * FROM te_events where eventID='$eventID' AND where venueID='$venueID' From te_venue AND where catID='$catID' From te_category";
$queryresult = mysqli_query($conn, $sql) or die(mysqli_error($conn));
while ($row = mysqli_fetch_array($queryresult)) {
$eventTitle = $row['eventTitle'];
$eventDescription = $row['eventDescription'];
$eventStartDate = $row['eventStartDate'];
$eventEndDate = $row['eventEndDate'];
$eventPrice = $row['eventPrice'];
$venueID = $row['venueID'];
$catID = $row['catID'];
$catName = $row['catDesc'];
$venueName = $row['venueName'];



}

?>


And i get this 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 'where venueID='' From te_venue AND where catID='' From te_category' at line 1

Answer

Yes, you can do that. But in order to get the fields that you want from all three tables, you have to join them together.

Look at this article on W3S: http://www.w3schools.com/sql/sql_join.asp. It explains SQL JOIN syntax and the basic theory behind.

If you just joined venue and event Your select statement looks like:

SELECT * FROM te_event 
JOIN te_venue 
ON te_vendue.venueID = te_event.venueID 
WHERE te_event.eventID = $eventID

The category table is similar.

Note: In general, use of SELECT * is discouraged. Your should list the fields that you want returned from the tables. ie. SELECT te_eventID, te_venueID

Comments