cronk005
By:
cronk005

If value not in one table query second table

June 17, 2017 172 views
PHP MySQL

I have a page that would pull two variables, q and vlgid from the URL string that is opened from a different page. i.e., image.php?q=911&vlgid=N829NW

Currently, my script would pull from just a single table where these two values are equal. However, I have a new table which is just for images.

What I'd like to know is if it's possible to pull an image from the new table, if it exists and if it does not yet exist in this table, pull it from the other table and then display it on the page.

$lvmID = mysqli_real_escape_string($mysqli, $_GET['q']); 
$vlgID = mysqli_real_escape_string($mysqli, $_GET['vlgid']); 

SELECT r.*, lvm.luchtvaartmaatschappijID AS lvmID, lvm.IATACode
FROM tbl_random r
LEFT JOIN tbl_luchtvaartmaatschappij lvm
ON r.img_lvm = lvm.IATACode
WHERE lvm.lvmID = '$lvmID' AND r.img_nmr = '$vlgID'
ORDER BY Rand()
LIMIT 1;

The current page uses this query. I don't like it, per se, because it ONLY has one result. Whereas, using the one above could result in many result possibilities.

$lvmID = mysqli_real_escape_string($mysqli, $_GET['q']); 
$vlgID = mysqli_real_escape_string($mysqli, $_GET['vlgid']); 

SELECT vlg.lvmID, lvm.luchtvaartmaatschappij, t.toestel, vlg.toestel, vlg.erlr, vlg.inschrijvingnmr, vlg.status, vlg.cn, vlg.ln, vlg.delivered, vlg.vliegtuignaam, vlg.became, vlg.vorigeLVMID, vlg.vorigeInschrijv, vlg.firstflight, vlg.engines, vlg.configuratie, vlg.remark, vlg.specialekleuren, vlg.fleetnmr, vlg.inactive, vlg.exitdate, vlg.beeld, lvm.logo, vlg.beeld_linkje, vlg.beeld_copyright, vlg.photouse_approve, vlg.photouse_approve_date, vlg.beeld_comment

FROM tbl_vliegtuiggegevens vlg

LEFT JOIN tbl_toestel t
ON vlg.toestelID = t.toestelID

LEFT JOIN tbl_luchtvaartmaatschappij lvm
ON vlg.lvmID = lvm.luchtvaartmaatschappijID

WHERE vlg.lvmID='$lvmID' AND vlg.inschrijvingnmr='$vlgID'AND vlg.photouse_approve IN ('GRANTED','APPROVED','WAITING','PENDING')
ORDER BY luchtvaartmaatschappij, t.toestel, vlg.inschrijvingnmr ASC;

I suppose that maybe a UNION query, could work? Though I'm not as familiar with doing those kind of queries.

If, there are no results from the first query, it should then look for a result in the second query and then I suppose I would echo results based on if/else? Something akin to this (which, for the moment, only uses the second query example

<?php
if ($row['photouseapprove'] == 'APPROVED'){
echo "<a href='" . $row['beeld
linkje'] . "' target='blank'><img src='" . $row['beeld'] . "' width='900'></a>";
}
else if($row['photouse
approve'] == 'GRANTED'){
echo "<a href='" . $row['beeld_linkje'] . "' target='_blank'><img src='" . $row['beeld'] . "' width='900'></a>";
}
else{
echo "<img src='photo_permission_pending.jpg' width='900'>";
}
?>

What are more experienced users thoughts on this?

1 Answer

Hi @cronk005

Without knowing your database structure or the application, I would probably just run two different queries and do the logic in PHP. One query asking the newTable and if result is empty, then query the oldTable.

You might get a better answer by asking in a MySQL specific forum:
https://stackoverflow.com/questions/tagged/mysql

  • Thanks, Hansen. I've also asked there, but have heard no response. I'm not quite sure how to do what you recommend but I'll keep looking.

Have another answer? Share your knowledge.