in array sql query

Question

I have the following inside a foreach loop (displaying my various videos), I'm trying to display some alternate text for the top three voted videos. What on earth am I doing wrong (a lot clearly)...

$sql = "SELECT video_id FROM videos WHERE displayable='y' ORDER BY votes desc LIMIT 0,3";
$result = mysql_query($sql);
$row = @mysql_fetch_array($result);

if(in_array($video->getProperty('video_id')) == $row['video_id']) {
do this...
} else {
do this..
}

RobertPitt · Accepted Answer · 2010-07-01 11:51:52Z

1

Firstly replace your code with some error preventing techniques like so!

$sql = "SELECT video_id FROM videos WHERE displayable='y' ORDER BY votes desc LIMIT 0,3";
if(false != ($result = mysql_query($sql))
{
   $row = mysql_fetch_assoc($result); //Dont need the @ restraint as the result is not false above.
   //Also to get associate keys you need to use mysql_fetch_assoc
   if($video->getProperty('video_id') == $row['video_id'])) //Remove the in array as your directly comparing the to entities with ==
   {
      //Match
   }else
   {
      //Video does not match
   }
}

Your main problem was the mysql_fetch_array(), Please research the differences with mysql_fetch_array() and mysql_fetch_assoc();

--

Edit: The way i would go

  //Change the query and the loop way.
  $sql = "SELECT video_id FROM videos WHERE displayable='y' AND video_id != '".(int)$video->getProperty('video_id')."' ORDER BY votes desc LIMIT 0,3";
    if(false != ($result = mysql_query($sql))
    //Use the @ restraint if you have E_NOTICE on within E_Reporting
    {
       while($row = mysql_fetch_assoc($result))
       {
            //Print the $row here how you wish for it to be displayed
       }
   }else
   {
       //We have an error?
       echo '<strong>Unable to list top rated videos, please check back later.</strong>'
   }
}

edited Jul 1, 2010 at 11:51

answered Jul 1, 2010 at 10:47

RobertPitt

57.3k21 gold badges117 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kalpaitch Over a year ago

yes thanks, I am aware of the differences, is see the importance.

RobertPitt Over a year ago

Another way you can do this is by modyfying the query to select hte top 3 bar the current video so like so

$sql = "SELECT video_id FROM videos WHERE displayable='y' AND  video_id != '".(int) $video->getProperty('video_id')."' ORDER BY votes desc LIMIT 0,3";

then you wont need the if() statement to remove the current video from the top 3 list.

Jon · Accepted Answer · 2010-07-01 10:46:00Z

1

mysql_fetch_array only returns a single row, you need to loop through your results to build an array containing the top three ids.

$sql = "SELECT video_id FROM videos WHERE displayable='y' ORDER BY votes desc LIMIT 0,3";
$result = mysql_query($sql);
while($row = @mysql_fetch_array($result)) {
  $topthree[] = $row["video_id"];
}

Then you can use in_array but with the correct syntax:

if(in_array($video->getProperty('video_id'), $topthree)) {
do this...
} else {
do this..
}

answered Jul 1, 2010 at 10:46

Jon

1612 bronze badges

3 Comments

kalpaitch Over a year ago

cheers for that, this is what I was aiming towards and works perfectly.

RobertPitt Over a year ago

He does not ahve a "$topthree" but the but he selecting the top 3 with the query and making sure he does not have the current playing video in the selected from db items.

kalpaitch Over a year ago

just to clarify, they both work very well, I felt I had to accept his answer as it was first and applied a shorter approach.

Collectives™ on Stack Overflow

in array sql query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related