Passing a variable into a MYSQL query statement...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Passing a variable into a MYSQL query statement...

Post by robster »

Hello all,

I hope that subject made sense, read on as I don't believe this is a clear cut variable passing question.


I have a site where I have created a cute little news script.
I have it displaying my news in ASC or DESC dependant on how I poll the database.

What I WANT to do is allow the viewer to decide whether or not to see the oldest or newest first.

In my file news_archives.php I have this menu on the left:

Code: Select all

<a href="/news_archives.php?direction=ASC">Date Ascending</a><br>
<a href="/news_archives.php?direction=DESC">Date Descending</a><br>
This I am presuming sends the strings ASC or DESC to itself (the same file) depending on which link the user clicks on. It seems to do this and the page reloads with the URL showing : http://localhost/news_archives.php?direction=ASC
The page loads, all is good...


NOW, In my script where the looping occurs that draws the news items from the database I have these lines:

Code: Select all

$travel = "$direction";
$content = mysql_db_query($dbname, "SELECT * FROM newscontent ORDER BY id $travel");
I am PRESUMING that the ORDER BY id $travel should pass the string ASC or DESC (depending on which the user has passed with their click) and should show the database contents (in this case news) in the appropriate order, either descending or ascending...

It doesn't though, it always seems to show it DESC.

Can anyone offer some help as to how I can pass that string to the MYSQL query?

Most thanks...

Rob
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

I should say I've tried this also and it doesn't work... Yes, I've read the thread re: reading about variables before posting...

Code: Select all

$travel = $_Post['direction'];
  	$content = mysql_db_query($dbname, "SELECT * FROM newscontent ORDER BY id $travel");
Again, any help would be.... AcE! :)
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

Try:

Code: Select all

$travel   = $_GET&#1111;'direction'];
// Make sure user hasn't set the direction variable to something other than DESC and ASC
if (($travel == "DESC") || ($travel == "ASC"))
&#123;
   $content = mysql_db_query($dbname, "Select * from newscontent order by id $travel");
&#125;
else &#123; &#125;  // Do nothing because the user has altered the value of direction
Tell me if it works.
If it doesn't you can do this:

Code: Select all

$travel = $_GET&#1111;'direction'];
switch ($travel)
&#123;
  case 'DESC':
    $content = mysql_db_query($dbname, "Select * from newscontent order by id DESC");
    break;
  case 'ASC':
    $content = mysql_db_query($dbname, "Select * from newscontent order by id"); // No need to append ASC to query because that is the default
    break;
  default:
    break;
&#125;
Hope this helps.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

thanks for the reply :)

BOTH of those methods gave me a string of these errors (only showing one here):

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\phpdev\www\vnews\news_archives.php on line 114

I should not that c:\phpdev\www\ is my localhost root and that the file c:\phpdev\www\vnews\news_archives.php is the actual news file that is embeded in c:\phpdev\www\news_archives.php (note the path differences).

I know it's BIT confusing, but they are two seperate files, both called the same but living in two different folders.. the one in root embeds (includes) the one in vnews inside itself...

ANYHOO...

Any ideas on the error, or any way to get it working?

Thanks again nigma for such a well thought out and coded reply.

Rob
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

hey!

My last post made me think. I'm passing the variable from the news_archives.php in the root to the EMBEDDED (included) php file that it takes from the vnews folder.

ARE the variables being passed to the embedded file?!?!

My guess is no, but I'd like to hear peoples thoughts on this.. Quite interesting really (if it weren't stopping me moving forward ;) )
RFairey
Forum Commoner
Posts: 52
Joined: Fri Jun 06, 2003 5:23 pm

Post by RFairey »

Try replacing

Code: Select all

$content = mysql_db_query($dbname, "Select * from newscontent order by id $travel");
with:

Code: Select all

$content = mysql_db_query($dbname, "Select * from newscontent order by id" . $travel);
thay way, $travel will be concatenated into your query - not sure if the first method gives the correct query.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

It's funny, this morning I sat down to see what happened on the thread, thanks to RFairey for your help :)

It turned out it gave the same errors as the others.
In a bit of depseration I copy and pasted this:

Code: Select all

$travel   = $_GET['direction'];
// Make sure user hasn't set the direction variable to something other than DESC and ASC
if (($travel == "DESC") || ($travel == "ASC"))
{
$content = mysql_db_query($dbname, "Select * from newscontent order by id $travel");
}
else { }  // Do nothing because the user has altered the value of direction

AND IT WORKED!!!! :) I don't know what happened last night. I have a sneaky feeling I left of the else statement... Bad me, I know.

My news section can now be viewed forward and backward.
Thanks so much to all you helpful people on this site. You're really helping me along and I'm learning loads in the process...

:)

Rob
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

One thing to add to this - mysql_db_query() is a deprecated function and shouldn't be used, instead of:

Code: Select all

$content = mysql_db_query($dbname, "Select * from newscontent order by id $travel");
Use something like:

Code: Select all

mysql_select_db($dbname);
$sql = "SELECT * FROM newscontent ORDER BY id $travel";
$content = mysql_query($sql);
Mac
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

Thank you :)
It didn't DO anything visually (as I'd expect it wouldn't) but I presume by what you're saying that this is the new way to do things, so thank you.

The problem I'm having learning is that I learn from places like forums etc where there are these magnificent knowlege bases and I can search them and learn loads, but they can be outdated.

Thanks so much for your info, I'll try and remember to do it that way from now on.

Thanks everyone for your help... Now I've got to figuire out how to make my news display in an archive format (ie: 5 stories per page, click next to view page 6-10, next again to view pages 11-15 etc etc)...

Spooky and exciting....

Rob
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

When I first saw this post I had never heard of mysql_db_query() so I looked it up in the PHP manual and sort of understood then why you would use that instead of mysql_query().

But just to make sure maybe you could tell me if I am thinking straight.

Use mysql_db_query() when you want to query a dbase once.

Use mysql_query() when you want to query it multiple times in the same script?

Tjhanks.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Don't use mysql_db_query() ever - it is deprecated, that means that it is not going to be in all future versions of PHP and is thus being phased out. As the manual says:
PHP Manual wrote:Note: This function has been deprecated since PHP 4.0.6. Do not use this function. Use mysql_select_db() and mysql_query() instead.
Mac
Post Reply