Jump to content

Retrieve first table id from left joined tables


lasha

Recommended Posts

Hello all! :)

 

I'm makeing car booking module and have one problem.

 

Explaining...

 

There is tree joined tables "books", "car" and "cat". When i joining this three table it retrieves ids from table "car", but i want ids from "books" what to do?

 

Can you help? Thanks :)

<?php 
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}


$query = "
SELECT * FROM books
LEFT JOIN car ON books.car_id = car.id
LEFT JOIN cat ON books.cat_id = cat.id
WHERE
books.owner = '$owner'";


$result = mysqli_query($db,$query) or die("Error: ".mysqli_error($db));
$myrow = mysqli_fetch_array($result, MYSQLI_BOTH);


if(mysqli_num_rows($result) > 0){


if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']');
}


do
{
printf ('<tr>
<td>'.$myrow["id"].'</td>
<td>'.$myrow["model"].'</td>
<td>'.$myrow["cat_name"].'</td>
<td>'.$myrow["name"].'</td>
<td>'.$myrow["surname"].'</td>
<td>'.$myrow["country"].'</td>
<td>'.$myrow["phone"].'</td> 
<td>'.$myrow["notes"].'</td>
<td>'.$myrow["start_date"].'</td>
<td>'.$myrow["end_date"].'</td>
<td>'.$myrow["owner"].'</td>
<td>'.$myrow["site"].'</td>
<td><a href="delete.php?id='.$myrow["id"].'">Delete</a></td>
</tr>');


}
while ($myrow = mysqli_fetch_array($result));
}
else{
echo '<p align="center" style="margin-top:100px;">There are no records...</p>';
}
?>

You are not doing yourself any favours by using "SELECT * " especially when using joined table queries. Always specify the fields you need and apply column aliases when required

 

eg

SELECT book.id as bookid
, cat.id as catid
, etc...

Thank you Barand. 

 

Yes it is solution.

 

I'll post how i did this

 

SELECT 


books.id as booksid, books.car_id, books.cat_id, books.name, books.surname, books.country, books.phone, books.notes, books.start_date, books.end_date, books.owner, books.site, car.id, cat.id, car.model, cat.cat_name


FROM books
LEFT JOIN car ON books.car_id = car.id
LEFT JOIN cat ON books.cat_id = cat.id
WHERE
books.owner = '$owner'";

and echoed like this

echo $myrow["booksid"];

Thank you again and again.  :)

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.