How can i change this select sql statement into another 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

You can't have multiple `from`s, nor multiple `where`s. Use joins. You also are open to SQL injections use parameterized queries. This also is very close to http://stackoverflow.com/questions/40600237/i-have-got-this-code-it-is-working-fine-but-i-want-to-change-the-having-code-int/40600378?noredirect=1#comment68511702_40600378 is part of some class? — chris85, Nov 16 '16 at 23:59
$sql = "SELECT * FROM te_events where eventID='$eventID' AND join venueID='$venueID' join te_venue AND where catID='$catID' join te_category"; like this ? — Benafsha Arya, Nov 17 '16 at 00:03
Either http://dev.mysql.com/doc/refman/5.7/en/join.html or look at the linked thread. — chris85, Nov 17 '16 at 00:04
If you are using that code it is because it is invalid. Again see linked thread or the manual. Post your updated code to the question. — chris85, Nov 17 '16 at 00:07
i used the first link in your comment but it did not work and yeah same class :D — Benafsha Arya, Nov 17 '16 at 00:21

score 1 · Accepted Answer · answered Nov 17 '16 at 00:14

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

answered Nov 17 '16 at 00:14

chuckrichmond

26
3

Table '.te_event' doesn't exist – Benafsha Arya Nov 17 '16 at 00:22
hey your code is working now just there was spelling mistake, just category and description field i get error any idea? – Benafsha Arya Nov 17 '16 at 01:01

atoms · Answer 2 · 2016-11-17T00:44:31.860

0

You could use joins as below;

SELECT * FROM te_events 
JOIN te_venue ON te_events.venueID = te_venue.venueID
JOIN te_category ON te_events.catID = te_category.catID
WHERE eventID = :EventID

As mentioned you should also use PDO's:

define( "DB_DSN", "mysql:host=localhost;dbname=foo");
define( "DB_USERNAME", "root");
define( "DB_PASSWORD", "password" ); 

// define sql
$sSQL = "SELECT * FROM te_events 
JOIN te_venue ON te_events.venueID = te_venue.venueID
JOIN te_category ON te_events.catID = te_category.catID
WHERE eventID = :EventID";

// create an instance of the connection
$conn   = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );

// prepare
$st   = $conn->prepare( $sSQL );

// securely bind any user input in the query
$st->bindValue(":EventID", $iEventID, PDO::PARAM_INT);

// execute the connection
$st->execute()

$aResults = array();

// loop over results and store in aResults
while($row = $st->fetch()){

    $aResults[] = $row;

}

// output data
foreach($aResults as $aResult){
    echo "title: ".$aResult['eventTitle'];
}

You should always avoid using select *. Especially when your doing joins. Ensure you request only what you need and alias if you require duplicate column names

edited Nov 17 '16 at 00:44

answered Nov 17 '16 at 00:13

atoms

2,993
2
22
43

I dont get any error with your first code but there is a lot event when i click on any i get same information for all suppose to be different each event has different info. this is event page https://postimg.org/image/fvym719bb/ and this information i get for all https://postimg.org/image/79q14pmm5/ – Benafsha Arya Nov 17 '16 at 00:25
Could it be you have a variation, it will show a row for each. You can consolidate these multiple rows in php – atoms Nov 17 '16 at 00:26
That while doesn't make sense, each time it loops it will overwrite the data. Use the method I've shown. Then loop over the array to create each part of output. If the sql is now working please mark as correct and open a new question for the output – atoms Nov 17 '16 at 00:32
my question is not solved and can you explain more please what should I do next then ? – Benafsha Arya Nov 17 '16 at 00:37
sorry i see no change in your sql i only used first part of your answer – Benafsha Arya Nov 17 '16 at 00:50

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

2 Answers2