Jump to content


Photo

MySQL Query Help


  • Please log in to reply
8 replies to this topic

#1 eRott

eRott
  • Members
  • PipPipPip
  • Advanced Member
  • 206 posts
  • LocationToronto, ON

Posted 02 September 2006 - 01:46 AM

Ok, for this code which you guys were helping me with, It searches and lists everything in the database. However, I am curious, how do I make it display ONLY the stuff with a specif id? For example, if I were to create a field in the table called 'video_type' and there were a bunch of different videos with different types such as 'funny', or 'violent', how would I get it to display ONLY the videos with the type of 'funny' ?

   // for each row fetched from the results...   
   while ($list = mysql_fetch_array($result)) {
       //make the custom linkie
       echo "<a href= '/videos/videos.php?id={$list['video_id']}'>{$list['video_name']}</a><br>"; 
   } // end while

If you need to see that thread where 'Crayon Violent' was helping me, it's here

Thanks.

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 02 September 2006 - 02:04 AM

The sensible way would be to modify the query and leave the code you posted alone.

$query = "SELECT * from ..... WHERE video_type='$type_chosen'";

And pass the variable type_chosen via link ... more or less.
Legend has it that reading the manual never killed anyone.
My site

#3 eRott

eRott
  • Members
  • PipPipPip
  • Advanced Member
  • 206 posts
  • LocationToronto, ON

Posted 02 September 2006 - 02:54 AM

Excellent. Thank you.

#4 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 02 September 2006 - 03:52 AM

Watch out for sql injection... Might want to make it
$type_chosen = addslashes($type_chosen);

Or someone could change the sql query...
Why doesn't anyone ever say hi, hey, or whad up world?

#5 eRott

eRott
  • Members
  • PipPipPip
  • Advanced Member
  • 206 posts
  • LocationToronto, ON

Posted 03 September 2006 - 12:14 AM

I am not too sure how I would go about using that code you provided. Could you explain a bit more please. I am not familiar with MySQL. Thank you.

This is the code:

$sql = "SELECT * from ..... WHERE video_type='funny'";

So should I change it to?:

$type_chosen = addslashes('funny');

$sql = "SELECT * from ..... WHERE video_type='$type_chosen'";


#6 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 03 September 2006 - 12:34 AM

I arbitrarily assumed that you would want code that could find funny or any other category without writing a separate script for every category. Thus, the script needs to have a variable to use for the type it needs to search for, rather than be hard-coded.

Let's assume that on one page you have a form, method=post and it has a dropdown select box name=type with all of your types as options ... when the form submit is clicked, the script that processes the data received from the form would get the video type you wanted by:

$type_chosen = $_POST['type']; // get the selected video type for the query

Legend has it that reading the manual never killed anyone.
My site

#7 eRott

eRott
  • Members
  • PipPipPip
  • Advanced Member
  • 206 posts
  • LocationToronto, ON

Posted 03 September 2006 - 01:00 AM

Ok, this is what I have. I have three pages:
http://erott.retect....ideos/funny.php
http://erott.retect....eos/violent.php
http://erott.retect....ideos/other.php

Take funny.php for example. All this is, is a simple page which displays a list of funny videos. A user then clicks the video and they are taken to it and it plays. You can see for yourself what i mean, just go to one of those pages.

Now, the code for funny.php (which is the same for the other pages as well, except the type is different, is:
<? include("../header.php");?>

<?php
   //connect to and select db
   include 'lib/config.php';
   include 'lib/opendb.php';
   
   //get a list of the info from the table to make the links
   $sql = "SELECT * from ..... WHERE video_type='funny'";
   $result = mysql_query($sql, $conn) or die(mysql_error());

   // for each row fetched from the results...   
   while ($list = mysql_fetch_array($result)) {
       //make the custom link
       echo "<a href= '/videos/videos.php?id={$list['video_id']}'>{$list['video_name']}</a><br>"; 
   } // end while

   include 'lib/closedb.php';

?>

<? include("../footer.php");?>

So, as you can see, i was not intending on having a user select the type. They just go to that page, and it will list all of the videos of that type for that page. (e.g. go to the 'funny' page and it will list all of the 'funny' videos.) So with that, how would I go about using this $type_chosen = addslashes($type_chosen); to protect against sql injections as corbin had stated?

#8 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 03 September 2006 - 02:44 AM

So with that, how would I go about using this $type_chosen = addslashes($type_chosen); to protect against sql injections as corbin had stated?

You don't need to worry about it all because you're not passing any variables (so no one can inject anything) and you have a 'hard-coded' query on each page.
Legend has it that reading the manual never killed anyone.
My site

#9 eRott

eRott
  • Members
  • PipPipPip
  • Advanced Member
  • 206 posts
  • LocationToronto, ON

Posted 04 September 2006 - 07:02 AM

Excellent. Thank you. :D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users