php mysql fetch two table

Question

i have two table from mysql.

1, photo (url_link)

2, video(url_link)

i wanna fetch only 1 table (video url) if both table aren't empty.. Is there anyways? am using code below:

<--photo -->
    <?php       
$query_img = mysql_query("SELECT `url` FROM `photos_news` WHERE title='{$thumn_content['titles']}' LIMIT 0,1");
while($img = mysql_fetch_array($query_img)){ ?>
<img src="<?php echo $img['url'];?>" class="img-responsive img-rounded"/>
<?php }?>


<!-- vdo -->
<?php
$query_vdo = mysql_query("SELECT `url` FROM `video_news` WHERE title='{$thumn_content['titles']}' LIMIT 0,1");
while($vdo = mysql_fetch_array($query_vdo)){ ?>
<div class="embed-responsive embed-responsive-16by9">
  <iframe class="embed-responsive-item" src="<?php echo $vdo['url']?>?modestbranding=1&showinfo=0&fs=0"></iframe>
</div>

<?php }?>

thank you sir,

use IS NULL of mysql to check empty – Saty Apr 25 '15 at 13:29 — Saty, Apr 25 '15 at 13:29

xNeyte · Answer 1 · 2015-04-25T14:43:41.263

0

You can do it with foreign keys and join beetween the two tables :)
you should also check about PDO to avoid sql injection in your code.

We can help you more if you give us how are your tables designed

TABLE video_news
idVideo
urlVideo
titleVideo

TABLE photo_news
idPhoto
urlPhoto
titlePhoto
idVideo = FOREIGN KEY

And so your statement to get urlVideo would be something like

SELECT urlVideo
FROM video_news vn, photo_news pn
WHERE vn.idVideo = pn.idVideo

In that way, if there is no photo which corresponds to the video, it will not fetch it

edited Apr 25 '15 at 14:43

answered Apr 25 '15 at 13:29

xNeyte

612
4
18

photos_news ==== > (id, url, title) video_news ===> (id, url, title) – Ah Sear Apr 25 '15 at 14:36
Does a photo_news belongs to a video_news ? If yes, you should create your tables like that (I edit my post) – xNeyte Apr 25 '15 at 14:41
sorry sir, i've one more table that is main table, TABLE `post` id, **title**, news TABLE photos_news id, url, **title**, TABLE `post` id, **title**, news TABLE vdo_news id url **title** – Ah Sear Apr 25 '15 at 14:58
can i use as code below `SELECT urlVideo FROM video_news vn, photo_news pn WHERE vn.idVideo = pn.idVideo and title='{$thumn_content['titles']}'` – Ah Sear Apr 25 '15 at 15:11
yes, just change "title" to "titlePhoto" or "titleVideo" after changing your database but you should check about using PDO for your statement else you are vulnerable to sql injection – xNeyte Apr 25 '15 at 15:48
Even with post table, you still don't have any relations beetween your tables in the database, you should recreate it from scratch :p – xNeyte Apr 25 '15 at 15:50

maalls · Accepted Answer · 2015-04-25T15:39:26.673

Well you can first fetch the photos and the videos as you did, but before writting the HTML. For example you can put your results into a $photos and $videos arrays.

then you can check if both variables has results, and if it is the case write only the HTML for the videos.

<?php // part 1: collect the data

$resource = mysql_query("SELECT `url` FROM `photos_news` WHERE title='{$thumn_content['titles']}' LIMIT 0,1");
$photos = array();

while($row = mysql_fetch_array($resource)) {

   $photos[] = $row;

}

$resource  = mysql_query("SELECT `url` FROM `video_news` WHERE title='{$thumn_content['titles']}' LIMIT 0,1");
$videos = array();

while($row = mysql_fetch_array($resource)) {

   $videos[] = $row;

}

// medias contains only $videos if both tables has entries.
$medias = $photos & $videos ? $videos : array_merge($photos, $videos);

?>
<?php // part 2: display the data ?>
<?php foreach($medias as $media) : ?>
   <img src="<?php echo $media['url'] ?>" class="img-responsive img-rounded"/>
<?php endforeach ?>

and cleaned up a bit to remove duplicated code. – maalls Apr 25 '15 at 15:44 — maalls, Apr 25 '15 at 15:44

php mysql fetch two table

2 Answers2