1

This is the code I have at the moment which works fine:

  <?php
    $sql = "SELECT * FROM te_events order by eventTitle ASC ";
    $result = $conn->query($sql);

    while($row = $result->fetch_assoc()) 
    {
        $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'];
        }

?>

But I want to Change it into this format. I could do only till this bit couldn't go further than this I get errors.

<?php
    $sql ="SELECT eventTitle, eventID, venueID, catID, eventStartDate, eventEndDate, eventPrice FROM te_events ORDER BY eventTitle ASC";
    $queryresult = mysqli_query($conn, $sql) or die(mysqli_error($conn));
    while ($row = mysqli_fetch_array($queryresult)) {

        $venueID = $row['venueID'];
        $catID = $row['catID'];
        $venueName = $row['venueName'];
        $catName = $row['catDesc'];

?>

How can I do that then?
how can I join two tables?

chris85
  • 23,846
  • 7
  • 34
  • 51
Abz Yahya
  • 23
  • 6

1 Answers1

0

You should be able to join the additional 2 tables to get the columns you need.

SELECT e.eventTitle, e.eventID, e.venueID, e.catID, e.eventStartDate, e.eventEndDate, e.eventPrice, v.venueName, c.catDesc
 FROM te_events as e
join te_venue as v
on e.venueID = v.venueID
join te_category as c
on c.catID = e.catID
ORDER BY eventTitle ASC

You also should avoid putting data directly into a query. If you need to do that use parameterized queries. This is how SQL injections (or second level) occur.

chris85
  • 23,846
  • 7
  • 34
  • 51
  • What did happened? Might need to give `eventTitle` its aliases in the `order by` as well, is that a unique name? – chris85 Nov 15 '16 at 01:02
  • Oh, I used the wrong aliases. Do you know what this code is doing? You should really understand something before you just go plugging it in. The `e`, `v`, and `c` are aliases for your tables. – chris85 Nov 15 '16 at 01:07
  • i still dont get it sorry :( – Abz Yahya Nov 15 '16 at 01:09
  • I've updated the answer, take a look. The `te_events as e` makes all `e.` reference the `te_events` table. If those columns are not in that table correct them, the same with the other `c.` and `v.`. – chris85 Nov 15 '16 at 01:10
  • Amazing I just copied and pasted that code everything came up right just one more thing how can i avoid SQL injection for this. – Abz Yahya Nov 15 '16 at 01:16
  • With this approach there is no injection possible. With your previous approach though if `$venueID` or `$catID` contained malicious code you would have been executing it. For future prevention use parameterized queries. See http://php.net/manual/en/mysqli.quickstart.prepared-statements.php for more information. Please accept this answer if it resolved your issue. – chris85 Nov 15 '16 at 01:23
  • I want to add "location" as well which is in te_venue table how can i add that as well in this sql statement – Abz Yahya Nov 16 '16 at 17:35
  • Location is already in one of these tables? Use whichever aliases it is and add it to the list of columns the `select` should pull. – chris85 Nov 16 '16 at 17:39
  • Okay, so see the second part of that comment. – chris85 Nov 16 '16 at 17:43
  • sorry didn't work here is a screenshot of my tables this might help https://postimg.org/image/6ui8nsi55/ – Abz Yahya Nov 16 '16 at 17:47
  • Whats the SQL you tried? What happened when you ran it, error? – chris85 Nov 16 '16 at 17:48
  • as v on e.venueID = v.location join te_venue – Abz Yahya Nov 16 '16 at 17:49
  • The table is already joined. Just add the column to the list of columns you want returned. – chris85 Nov 16 '16 at 17:49
  • just e.venueID = v.location? – Abz Yahya Nov 16 '16 at 17:50
  • Just add `v.location` to the list of columns. – chris85 Nov 16 '16 at 17:51
  • Now for updating I got this SQL statement "UPDATE te_events SET eventTitle='$title',eventStartDate='$startdate',eventEndDate='$enddate',eventPrice='$price',eventDescription='$description' WHERE eventID=$eventID"; How can i add location in this ? – Abz Yahya Nov 16 '16 at 18:06
  • These are all separate questions then what you initially asked. See http://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query – chris85 Nov 16 '16 at 18:09
  • sorry that page was not helpful – Abz Yahya Nov 16 '16 at 18:20
  • You need to apply it to your code, it should be the same concept. Ask another question if you are having issues. Include the code and the error(s) (if any). – chris85 Nov 16 '16 at 18:28
  • hey chris this is the link for my new question http://stackoverflow.com/questions/40639779/how-to-add-to-this-update-sql-statement-and-make-a-drop-down-list-too-for-it – Abz Yahya Nov 16 '16 at 18:40