mysql and url issue in php

[V1ktor]

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.

 

[dbradbury]

 <?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;");

[V1ktor]

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.

[dbradbury]

well there is no where in your code, that i can see, gets any info from an 'issues' table

[V1ktor]

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.

[dbradbury]

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...

[V1ktor]

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.

[dbradbury]

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..

[dbradbury]

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");

 

[V1ktor]

Nope, nothing.

[dbradbury]

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 `  `...

[V1ktor]

NICE! It works. I didn't even see the difference until you pointed out. Thanks man! 

[dbradbury]

you're welcome!

 

im happy! you are actually the first person ive successfully helped! xD