V1ktor Posted February 16, 2010 Share Posted February 16, 2010 I have this piece of code that pulls an some info from database to create image with title, hyperlinked to an article. The problem is that the link to article changed, and I'm having trouble pulling required piece from database and inserting it here. <?php $onlinecomics = mysql_query("SELECT `articles`.`slug`, `image_id`, `name` FROM `articles` JOIN `tagstoarticles` ON `articles`.`id` = `tagstoarticles`.`article_id` WHERE `articles`.`status` = 'published' AND `tagstoarticles`.`tag_id` = '350' ORDER BY `date` DESC LIMIT 1;"); $onlinecomic = mysql_fetch_assoc($onlinecomics); ?> <table cellpadding="0" cellspacing="0" width="95%" style="padding-bottom:10px;" class="online"> <tr> <td colspan="2"> <h2>Online Cartoon</h2> </td> </tr> <tr> <td style="padding-bottom:4px" colspan="2"><a href="/article/INSERT HERE/<?php echo urlencode($issue['slug']); ?>/<?php echo urlencode($onlinecomic['slug']); ?>"><img src="/files/cache/<?php echo intval($onlinecomic['image_id']); ?>_smartsize_240_160.jpg" style="border:1px solid #999999" width="240" height="160"></a></td> </tr> <tr> <td style="padding-bottom:5px" align="center" colspan="2"> <span class="title"><a href="/article/INSERT HERE/<?php echo urlencode($onlinecomic['slug']); ?>"><?php echo htmlentities($onlinecomic['name']); ?></a></span> </td> </tr> </tr> </table> I have table called "issues" where one of the columns "slug" is the required piece for the new url to work. I've included 2 "INSERT HERE" in the code so you can see where "slug" needs to go. The information is ordered by date and the most recent item is pulled. I tried the same method for "slug" as used for "onlinecomic", but I can't get it to work. I would really appreciate help any help. Quote Link to comment Share on other sites More sharing options...
dbradbury Posted February 16, 2010 Share Posted February 16, 2010 <?php $onlinecomics = mysql_query("SELECT `articles`.`slug`, `image_id`, `name` FROM `articles` JOIN `tagstoarticles` ON `articles`.`id` = `tagstoarticles`.`article_id` WHERE `articles`.`status` = 'published' AND `tagstoarticles`.`tag_id` = '350' ORDER BY `date` DESC LIMIT 1;"); $onlinecomic = mysql_fetch_assoc($onlinecomics); ?> <table cellpadding="0" cellspacing="0" width="95%" style="padding-bottom:10px;" class="online"> <tr> <td colspan="2"> <h2>Online Cartoon</h2> </td> </tr> <tr> <td style="padding-bottom:4px" colspan="2"><a href="/article/INSERT HERE/<?php echo urlencode($issue['slug']); ?>/<?php echo urlencode($onlinecomic['slug']); ?>"><img src="/files/cache/<?php echo intval($onlinecomic['image_id']); ?>_smartsize_240_160.jpg" style="border:1px solid #999999" width="240" height="160"></a></td> </tr> <tr> <td style="padding-bottom:5px" align="center" colspan="2"> <span class="title"><a href="/article/INSERT HERE/<?php echo urlencode($onlinecomic['slug']); ?>"><?php echo htmlentities($onlinecomic['name']); ?></a></span> </td> </tr> </tr> </table> you tried this instead? $onlinecomics = mysql_query("SELECT * FROM `articles` JOIN `tagstoarticles` ON `articles`.`id` = `tagstoarticles`.`article_id` WHERE `articles`.`status` = 'published' AND `tagstoarticles`.`tag_id` = '350' ORDER BY `date` DESC LIMIT 1;"); Quote Link to comment Share on other sites More sharing options...
V1ktor Posted February 16, 2010 Author Share Posted February 16, 2010 Thanks for quick response. onlinecomic part works fine. The query pulls info from "articles" table, but I need issue slug to be pulled from another table "issues". This is where I'm having problems. I tried similar method of pulling "slug" from "issues" like onlinecomic does, but it's not working. onlinecomic inserts article slug in the url, but it's missing issue slug to work properly. Thanks. Quote Link to comment Share on other sites More sharing options...
dbradbury Posted February 16, 2010 Share Posted February 16, 2010 well there is no where in your code, that i can see, gets any info from an 'issues' table Quote Link to comment Share on other sites More sharing options...
V1ktor Posted February 16, 2010 Author Share Posted February 16, 2010 well there is no where in your code, that i can see, gets any info from an 'issues' table Exactly. I need to add that. My attempts were resulting in errors. Quote Link to comment Share on other sites More sharing options...
dbradbury Posted February 16, 2010 Share Posted February 16, 2010 ahh lol i see, read it completely wrong before lol, sorry... <?php $onlinecomics = mysql_query("SELECT `articles`.`slug`, `image_id`, `name` FROM `articles` JOIN `tagstoarticles` ON `articles`.`id` = `tagstoarticles`.`article_id` WHERE `articles`.`status` = 'published' AND `tagstoarticles`.`tag_id` = '350' ORDER BY `date` DESC LIMIT 1;"); $onlinecomic = mysql_fetch_assoc($onlinecomics); $getinfofromissues = mysql_query("SELECT * FROM `issues` [and with what search specifics you need...]"); $getinfofromissue = mysql_fetch_assoc($getinfofromissues); ?> <table cellpadding="0" cellspacing="0" width="95%" style="padding-bottom:10px;" class="online"> <tr> <td colspan="2"> <h2>Online Cartoon</h2> </td> </tr> <tr> <td style="padding-bottom:4px" colspan="2"><a href="/article/[if code goes here..]<?php echo $getinfofromissue['slug']; ?>/<?php echo urlencode($issue['slug']); ?>/<?php echo urlencode($onlinecomic['slug']); ?>"><img src="/files/cache/<?php echo intval($onlinecomic['image_id']); ?>_smartsize_240_160.jpg" style="border:1px solid #999999" width="240" height="160"></a></td> </tr> <tr> <td style="padding-bottom:5px" align="center" colspan="2"> <span class="title"><a href="/article/<?php echo $getinfofromissue['slug']; ?>/<?php echo urlencode($onlinecomic['slug']); ?>"><?php echo htmlentities($onlinecomic['name']); ?></a></span> </td> </tr> </tr> </table> what about that? i didnt know if you need urlencode on it or anything... Quote Link to comment Share on other sites More sharing options...
V1ktor Posted February 16, 2010 Author Share Posted February 16, 2010 Ok. great, now it works. I don't know what I was doing haha. Here's the new issue, getting recent date. I tried using values that comiconline uses ORDER BY and DESC LIMIT, but it pulls up another date... not the one I need. $getinfofromissues = mysql_query("SELECT * FROM `issues` ORDER BY 'date' DESC LIMIT 1;"); issues table does have "date" column, but I guess I'm missing something that it doesn't work for this but works for other. Thanks again. Quote Link to comment Share on other sites More sharing options...
dbradbury Posted February 16, 2010 Share Posted February 16, 2010 so you want the earliest one that has been posted...? what is the format for the date column, is it a time stamp like 1325746821? lol, a bunch of number...? if so ive done something similiar to this, getting the recent on to be displayed.. Quote Link to comment Share on other sites More sharing options...
dbradbury Posted February 16, 2010 Share Posted February 16, 2010 hold on!! take out the semicolon ';' in the brackets at the end... like this: $getinfofromissues = mysql_query("SELECT * FROM `issues` ORDER BY 'date' DESC LIMIT 1"); Quote Link to comment Share on other sites More sharing options...
V1ktor Posted February 16, 2010 Author Share Posted February 16, 2010 Nope, nothing. Quote Link to comment Share on other sites More sharing options...
dbradbury Posted February 16, 2010 Share Posted February 16, 2010 this may be really silly, but php is stubborn lol but here.. $getinfofromissues = mysql_query("SELECT * FROM `issues` ORDER BY 'date' DESC LIMIT 1;"); change to $getinfofromissues = mysql_query("SELECT * FROM `issues` ORDER BY `date` DESC LIMIT 1"); it may be the ' ' around date, as they werent ` `... Quote Link to comment Share on other sites More sharing options...
V1ktor Posted February 16, 2010 Author Share Posted February 16, 2010 NICE! It works. I didn't even see the difference until you pointed out. Thanks man! Quote Link to comment Share on other sites More sharing options...
dbradbury Posted February 16, 2010 Share Posted February 16, 2010 you're welcome! im happy! you are actually the first person ive successfully helped! xD Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.