如何将这个select sql语句更改为另一个select sql？

Question

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 1

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