?
Solved

php / mysql

Posted on 2007-10-12
12
Medium Priority
?
341 Views
Last Modified: 2013-12-13
hi,
i have a video database ( mysql / PHP ) and i want to have an xml file to show 10 random videos from the database.

the result xml should look like this

      <VIDEO Title="video1" Artist="artist1" />


        <VIDEO Title="video2" Artist="artist2" />


...

          <VIDEO Title="video10" Artist="artist10" />


can you help me with the select statement and the echo statemetn?
i am guessing it should be something like

$query = "SELECT * FROM video RANDOM 10";

and we should echo the 10 reslults..

any help is greatlly appreciated.
thanks

0
Comment
Question by:djsoltan
  • 6
  • 3
  • 3
12 Comments
 
LVL 21

Accepted Solution

by:
nizsmo earned 1600 total points
ID: 20070064
To limit query to 10 results:

$query = "SELECT * FROM video ORDER BY RANDOM() LIMIT 0,10";

Then you can do this to execute query:
$result = mysql_query($query); // Make sure you have connected to the database...

while($row = mysql_fetch_array($result))
{
    echo $row["column_name"];
}

Where column_name is your table column name.

Let me know how you go.

0
 
LVL 6

Expert Comment

by:karlwilbur
ID: 20070085
I don't think that MySQL supports fetching random records. Assuming that you have a numberic id value for each record, with no gaps in the numbering, you could do this:

// select the min and max id values from the database
$query  ="SELECT MIN(idVideo) AS min, MAX(idVideo) AS max  FROM video";
$result = mysql_query($query);
$min = mysql_result($result,0,'min');
$max = mysql_result($result,0,'max');

// create array for storing the random values
$rand_vals = array();

// set number of records to fetch
$numberOfVideosToFetch  = 10;

// get the right number of random values
for ($i = 0; $i <= $numberOfVideosToFetch; $i++) {
   $val = rand($min, $max);
    // make sure the each is unique
   if (!in_array($val, $rand_vals)) $rand_vals[] = $val;
}

// build query iwth random values
$query = "SELECT * FROM video WHERE idVideo IN (";
$add_comma = false;
foreach($rand_vals as $val) {
   if ($add_comma) $query .= ",";
   $query .= $val;
   $add_comma = true;
}
$query .= ")";
$result = mysql_query($query);

// output XML
while($row = mysql_fetch_assoc($result)) {
   echo "<VIDEO Title='".$row['video']."' Artist='".$row['artist']."' />\n";
}

I know that it's kind of a quick and dirty solution, but it'd work.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20070092
My mistake I did mistype please replace my query with RANDOM() with RAND() and it should work.

@karlwilbur:

Yes MySQL does support random retrievals of rows in the database:
http://akinas.com/pages/en/blog/mysql_random_row/
http://www.mustap.com/databasezone_post_141_mysql-rand-function
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:karlwilbur
ID: 20070099
nizsmo's solution is much better. I didn't think of using RANDOM() as an ORDER BY.

Also, if you use mysql_fetch_array($result) instead of mysql_fetch_assoc($result) you cannot use the column name, you need to use the column numeric index...unless you do mysql_fetch_array($result, MYSQL_ASSOC) or mysql_fetch_array($result, MYSQL_BOTH)
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20070110
@karlwilbur:

Thanks for clarification :-)
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20070111
@karlwilbur:

Thanks for clarification :-)
0
 

Author Comment

by:djsoltan
ID: 20070156
hi guys,
thank you for the responses.

Nizsmo

your code works, but it doesnt really return what i really need.

i need the results to be like this.


      <VIDEO Title="title1" Artist="artist1" />


          <VIDEO Title="title2" Artist="artist2" />


...

            <VIDEO Title="title3" Artist="artist3" />

i played around, and this code kinda does this, but i dont think its the proper way of doing it since i am using too many echos

while($row = mysql_fetch_array($result))

{
       echo "<VIDEO Title=\"";
      echo $row["title"];
      echo "\"";
      echo " ";
      
      
       echo "artist=\"";
      echo $row["artist"];
      echo "\"";
      echo " ";
      
echo "/>";

can you help with this please?

thank you in advance
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20070167
Can you clarify what you mean by "you are using too many echos"? You can echo the results out 1 by 1 using the while loop.

Look forward to your clarification.
0
 

Author Comment

by:djsoltan
ID: 20070179
ok, so the result should look like this


      <VIDEO Title="title1" Artist="artist1" />


          <VIDEO Title="title2" Artist="artist2" />


...

            <VIDEO Title="title3" Artist="artist3" />

so... my question is,, how do i get the following results?

i can use this code to get it

       echo "<VIDEO Title=\"";
      echo $row["title"];
      echo "\"";
      echo " ";
     
     
       echo "artist=\"";
      echo $row["artist"];
      echo "\"";
      echo " ";

but if you notice, i am using 8 echo statements,,, and i thought this would waste the server resources,,, ( would it? )

so i wanted to know if there is a better way of echoing the results

maybe something like

echo "<VIDEO Title="$title" Artist="$artist" />";

( this actually doesnt work,,, but something close to this )

thank you :)
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20070191
oh i see what you mean.

something like this:

echo "<VIDEO Title=\"" . $row["title"] . "\" artist=\"" . $row["artist"] . "\" "; // etc....

would work. So you get the idea, to connect 2 strings you need to put a "." in between... then you can echo this 1 massive query as you wish.

By the way I do not think echo wastes that much resources, if any.

Let me know how you go.
0
 
LVL 6

Assisted Solution

by:karlwilbur
karlwilbur earned 400 total points
ID: 20070327
djsoltan,
The number of echo statements are really _not_ going to be an issue. However, the code looks, IMO, much cleaner and easier to read written as:
echo '<VIDEO Title="'.$row["title"].'" artist="'.$row["artist"].'">'."\n";

rather than:
echo '<VIDEO Title="';
echo $row["title"];
echo '" artist="';
echo $row["artist"];
echo '">'."\n";

But use what ever works for you and your team...just stick to it.
0
 

Author Comment

by:djsoltan
ID: 20070573
thank you very much guys :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question