Abz Yahya Abz Yahya - 10 months ago 43
MySQL Question

I have got this code it is working fine but i want to change the having code into another structure

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

$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 **

$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'];


Answer Source

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.