php / mysql

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

djsoltanAsked:
Who is Participating?
 
nizsmoDeveloperCommented:
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
 
karlwilburCommented:
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
 
nizsmoDeveloperCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
karlwilburCommented:
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
 
nizsmoDeveloperCommented:
@karlwilbur:

Thanks for clarification :-)
0
 
nizsmoDeveloperCommented:
@karlwilbur:

Thanks for clarification :-)
0
 
djsoltanAuthor Commented:
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
 
nizsmoDeveloperCommented:
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
 
djsoltanAuthor Commented:
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
 
nizsmoDeveloperCommented:
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
 
karlwilburCommented:
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
 
djsoltanAuthor Commented:
thank you very much guys :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.