Results 1 to 28 of 28
  1. #1
    Join Date
    Jul 2003
    Posts
    637

    How to query rows who's ID's exist instead of any row

    The following script seems to select any row 1 through 99 since there are 99 rows in the database. However, we always delete some rows. For example, row with id number 24 might be deleted. But the script can still select it. How would we modify this script so that it only select rows who's id still exists in the database?

    PHP Code:
    $q mysql_query("SELECT id FROM videos") or die(mysql_error());
    $num mysql_num_rows($q);
    $d date("z");

    $id $d $num;

    $q mysql_query("SELECT videotitle,videourl FROM videos WHERE id='$id'");
    $a mysql_fetch_array($q);
    $a[videourl] = str_replace("\n","",$a[videourl]);
    $url explode("?v=",$a[videourl]);
    $url $url[1]; 

  2. #2
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    The script is selecting rows that don't exist because instead of only pulling from the pool of id's it selected in the previous statement, it's using the count of all the records to arrive at a random number. This random number, of course, may not always exist, because it's never checked against the pool of existing id's from the same table.
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  3. #3
    Join Date
    Jun 2006
    Posts
    1,111
    What is it you're trying to select, a random row from the table? Sorry if I'm completely out but if that's what you're trying to select, then try this:

    PHP Code:
    $q mysql_query("SELECT videotitle,videourl FROM videos ORDER BY RAND() LIMIT 1;'"); 

  4. #4
    Join Date
    Apr 2003
    Location
    Melbourne, AU
    Posts
    539
    A quick hack would be to change that second query to this:
    Code:
    $q = mysql_query("SELECT videotitle,videourl FROM videos LIMIT $id,1");
    Just a quick hack, mind you..
    WK Woon
    CTO | http://www.aflexi.net - A flexible Network
    Building the next generation CDN platform - DEMO .... coming soon

  5. #5
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    Quote Originally Posted by wKkaY View Post
    A quick hack would be to change that second query to this:
    Code:
    $q = mysql_query("SELECT videotitle,videourl FROM videos LIMIT $id,1");
    Just a quick hack, mind you..
    That's not going to work, because the row could still not exist. I have a script that deals with this exact problem that you might want to take a look at - http://www.czaries.net/scripts/ - It's called CzarRand. Basically, it builds an array of ID's that it finds from the table, and uses array_rand() to get a random value of only the existing rows in the table.

    You should not use MySQL's RAND() function, as it's quite slow on larger tables - slower than using two more efficient queries.
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  6. #6
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    I'll go ahead and add some code here for you to try. This is just off the top of my head, so it hasen't been tested, but it should work:

    PHP Code:
    $q mysql_query("SELECT id FROM videos") or die(mysql_error());
    $num mysql_num_rows($q);
    $d date("z");

    $i 0;
    while(
    $row mysql_fetch_row($q))

        
    $rand[$i] = $row[0];
        
    $i++;
    }
    mysql_free_result($q);

    $id $rand[array_rand($rand)); 
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  7. #7
    Join Date
    Jul 2007
    Location
    United States
    Posts
    19
    Quote Originally Posted by Czaries View Post
    Quote Originally Posted by wKkaY View Post
    A quick hack would be to change that second query to this:
    Code:
    $q = mysql_query("SELECT videotitle,videourl FROM videos LIMIT $id,1");
    Just a quick hack, mind you..
    That's not going to work, because the row could still not exist. I have a script that deals with this exact problem that you might want to take a look at - http://www.czaries.net/scripts/ - It's called CzarRand. Basically, it builds an array of ID's that it finds from the table, and uses array_rand() to get a random value of only the existing rows in the table.
    That 'quick hack' should work actually. Limit doesn't select by the row's id but by how many rows are in the table.

  8. #8
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Quote Originally Posted by ZackN View Post
    That 'quick hack' should work actually. Limit doesn't select by the row's id but by how many rows are in the table.
    I don't think the 'quick hack' would error out but it would give increasingly biased results as the number of deletions increases.

    eg. with 98 rows in the table (1-99 with 24 deleted), 25 would have double the correct probability of being chosen and 99 would never be chosen.

    I would go with ST-Mike's "ORDER BY RAND() LIMIT 1" unless it causes performance issues. For 100 rows, it won't be a problem; for larger tables it would be worth testing to compare against Czaries method and see which is really faster.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  9. #9
    Join Date
    Jul 2003
    Posts
    637
    I tried the LIMIT method but it did not work. It still selected a record from the database which was deleted (record id #1). Here is the code:

    PHP Code:
    $q mysql_query("SELECT id FROM videos") or die(mysql_error());
    $num mysql_num_rows($q);
    $d date("z");

    $id $d $num;

    $q mysql_query("SELECT videotitle,videourl FROM videos WHERE id='$id' ORDER BY RAND() LIMIT 1;");
    $a mysql_fetch_array($q);
    $a[videourl] = str_replace("\n","",$a[videourl]);
    $url explode("?v=",$a[videourl]);
    $url $url[1]; 
    I also tried Czaries method, but the issue is that it needs to rotate only once per day. These methods make the script rotate on every refresh of the page.

  10. #10
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    I've commented out the stuff you no longer need...
    PHP Code:
    /*
    $q = mysql_query("SELECT id FROM videos") or die(mysql_error());
    $num = mysql_num_rows($q);
    $d = date("z");

    $id = $d % $num;

    $q = mysql_query("SELECT videotitle,videourl FROM videos WHERE id='$id' ORDER BY RAND() LIMIT 1;");
    */
    $q mysql_query("SELECT videotitle,videourl FROM videos ORDER BY RAND() LIMIT 1;");
    $a mysql_fetch_array($q);
    $a[videourl] = str_replace("\n","",$a[videourl]);
    $url explode("?v=",$a[videourl]);
    $url $url[1]; 
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  11. #11
    Join Date
    Jul 2003
    Posts
    637
    Hi foobic,

    Thanks. But it doesn't work as it's supposed to like my old code. It's supposed to be "video of the day" which rotates the row the query returns once per day. The code you posted rotates on every page refresh.

  12. #12
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Ah, ok. I didn't see you had a date("z") in there. But it's hardly efficient to use 2 database queries on every page request just to get something that only changes once a day. How about running the above query in a daily cron job and saving the url to a file, then using php to include that file?
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  13. #13
    Join Date
    Jul 2003
    Posts
    637
    Yes you're right.

  14. #14
    Join Date
    Apr 2003
    Location
    Melbourne, AU
    Posts
    539
    Quote Originally Posted by Liguidsoul View Post
    I tried the LIMIT method but it did not work. It still selected a record from the database which was deleted (record id #1).
    When you "delete" a video, do you actually delete the row from the database, or do you set a status flag marking it as deleted?
    WK Woon
    CTO | http://www.aflexi.net - A flexible Network
    Building the next generation CDN platform - DEMO .... coming soon

  15. #15
    Join Date
    Jul 2003
    Posts
    637
    Delete the row from the database.

  16. #16
    Join Date
    Apr 2003
    Location
    Melbourne, AU
    Posts
    539
    Then I cannot see how my method would select a deleted row. It simply cannot It will select a random row for the day.. and the row won't have a video id of $id, but rather it would be the $id'th row that happens to be in the table/index at that moment.

    Agreed with foobic on the matter of using two queries, there are better ways to execute it, but for now I would rather solve the problem first
    WK Woon
    CTO | http://www.aflexi.net - A flexible Network
    Building the next generation CDN platform - DEMO .... coming soon

  17. #17
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Quote Originally Posted by wKkaY View Post
    Then I cannot see how my method would select a deleted row. It simply cannot It will select a random row for the day.. and the row won't have a video id of $id, but rather it would be the $id'th row that happens to be in the table/index at that moment.
    Yes, you're right - it should work (and my comment about bias was wrong - duh!). To be on the safe side it would be a good idea to add an "ORDER BY id" clause to ensure that a particular value of id always returns the same record.

    However, I suspect the program has a different problem anyway. If you look at how the "random" id is selected (as I did, eventually ) what it's actually doing is just stepping through all available entries with (day_number % number_of_entries). So a small increase in the number of entries could easily send it back to repeat the same sequence as a few days before.

    Another way to generate a pseudo-random but consistent daily (id) would be this:
    PHP Code:
    srand(date("z"));
    $id=rand(0,$num); 
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  18. #18
    Join Date
    Jul 2007
    Posts
    522
    Why must everyone make the simplest things so hard to do with all this randomization crap, all you had to do was change this:

    PHP Code:
    $q mysql_query("SELECT id FROM videos") or die(mysql_error()); 
    $num mysql_num_rows($q); 
    $d date("z"); 

    $id $d $num

    $q mysql_query("SELECT videotitle,videourl FROM videos WHERE id='$id'"); 
    $a mysql_fetch_array($q); 
    $a[videourl] = str_replace("\n","",$a[videourl]); 
    $url explode("?v=",$a[videourl]); 
    $url $url[1]; 
    to:


    PHP Code:
    $q mysql_query("SELECT id FROM videos") or die(mysql_error()); 
    $num mysql_num_rows($q); 
    $d date("z"); 

    $id $d $num

    $q mysql_query("SELECT videotitle,videourl FROM videos WHERE id='$id'"); 
    $a mysql_fetch_array($q);

    if (!
    $a[videourl]) {
    //stuff you want to happen if videourl isn't set
    } else {
    $a[videourl] = str_replace("\n","",$a[videourl]); 
    $url explode("?v=",$a[videourl]); 
    $url $url[1];

    Looking for Work level 1-3 Support Tech, Sales/billing tech, Odd management Jobs, and PHP/Mysql small jobs.
    PM me or drop me an email at mm(AT)gotannex(dot)com
    http://monitor.gotannex.com/

  19. #19
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    Annex, that's not exactly a good solution. With that, it would be possible to not get any row at all, and thus a video would not be displayed. The desired result is that a video is ALWAYS displayed.

    Doing a "LIMIT $id,1" should work for your purposes, and it will always return a result as long as there are rows in the database. I read the query wrong at first and didn't realize the $id variable was placed in the LIMIT clause. wKkay's solution should work great.
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  20. #20
    Join Date
    Jul 2007
    Posts
    522
    Quote Originally Posted by Czaries View Post
    Annex, that's not exactly a good solution. With that, it would be possible to not get any row at all, and thus a video would not be displayed. The desired result is that a video is ALWAYS displayed.

    Doing a "LIMIT $id,1" should work for your purposes, and it will always return a result as long as there are rows in the database. I read the query wrong at first and didn't realize the $id variable was placed in the LIMIT clause. wKkay's solution should work great.
    Did you even read what I wrote or did you just disclaim it because I said your was clunky and unnecessary, look again

    PHP Code:
    $q mysql_query("SELECT id FROM videos") or die(mysql_error()); 
    $num mysql_num_rows($q); 
    $d date("z"); 

    $id $d $num

    $q mysql_query("SELECT videotitle,videourl FROM videos WHERE id='$id'"); 
    $a mysql_fetch_array($q);

    if (!
    $a[videourl]) {
    //stuff you want to happen if videourl isn't set
    } else {
    $a[videourl] = str_replace("\n","",$a[videourl]); 
    $url explode("?v=",$a[videourl]); 
    $url $url[1];

    Its a much easier method.
    Looking for Work level 1-3 Support Tech, Sales/billing tech, Odd management Jobs, and PHP/Mysql small jobs.
    PM me or drop me an email at mm(AT)gotannex(dot)com
    http://monitor.gotannex.com/

  21. #21
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Quote Originally Posted by Annex View Post
    PHP Code:
    //stuff you want to happen if videourl isn't set 
    Its a much easier method.
    What method exactly? It looks like your "solution" is to add extra code (unspecified) to solve the problem. Hmm...

    As I see it the original code has 3 problems:
    1. It fails with deleted records (the OP's original issue)
    2. It's wasteful of resources (2 database queries on every page load for no good reason)
    3. It's liable to skip back a day or two when additional records are added (so visitors may notice that the "video of the day" is the same as yesterday or the day before).

    I'd just throw the whole code-block away and choose a record-of-the-day in a daily cron job. Since it's supposed to be sequential rather than random I'd also add a "lastshown" date field to the videos table and ORDER BY that , which would ensure maximum time between repeats, ie.
    Code:
    SELECT id,videotitle,videourl FROM videos ORDER BY lastshown LIMIT 1;
    ...
    UPDATE videos SET lastshown=NOW() WHERE id=$id;
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  22. #22
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    Quote Originally Posted by Annex View Post
    Did you even read what I wrote or did you just disclaim it because I said your was clunky and unnecessary, look again
    PHP Code:
    //stuff you want to happen if videourl isn't set 
    Its a much easier method.
    Wow... Annex, please relax instead of trying to defend yourself. That statement was not a personal attack, but rather a statement of truth. The code you posted does not provide the thread starter with a real solution to their problem. There is more code that will have to be written there to handle the "stuff you want to happen if videourl isn't set", which doesn't help him, especially when he specified that he ALWAYS wants a video to be displayed.

    Please step back and look at what I was trying to say. I don't have any problem whatsoever with the poster not using my solution. As I said in my previous post, wKkay posted an excellent solution that should be used instead of my own. In a forum like this, you need to be open to constructive criticism on anything you post. No one is trying to put you down here - we are simply trying to come up with the best solution to a problem.
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

  23. #23
    Join Date
    Jul 2007
    Posts
    522
    Quote Originally Posted by foobic View Post
    What method exactly? It looks like your "solution" is to add extra code (unspecified) to solve the problem. Hmm...

    As I see it the original code has 3 problems:
    1. It fails with deleted records (the OP's original issue)
    2. It's wasteful of resources (2 database queries on every page load for no good reason)
    3. It's liable to skip back a day or two when additional records are added (so visitors may notice that the "video of the day" is the same as yesterday or the day before).
    I'd just throw the whole code-block away and choose a record-of-the-day in a daily cron job. Since it's supposed to be sequential rather than random I'd also add a "lastshown" date field to the videos table and ORDER BY that , which would ensure maximum time between repeats, ie.
    Code:
    SELECT id,videotitle,videourl FROM videos ORDER BY lastshown LIMIT 1;
    ...
    UPDATE videos SET lastshown=NOW() WHERE id=$id;
    Heres what the OP asked:

    The following script seems to select any row 1 through 99 since there are 99 rows in the database. However, we always delete some rows. For example, row with id number 24 might be deleted. But the script can still select it. How would we modify this script so that it only select rows who's id still exists in the database?
    He wanted to find how he could pick rows that only exist. I added the code

    PHP Code:

    if (!$a[videourl]) {
    //stuff you want to happen if videourl isn't set
    } else {
    ->
    $a[videourl] = str_replace("\n","",$a[videourl]); 
    ->
    $url explode("?v=",$a[videourl]); 
    ->
    $url $url[1];

    (anything with -> leading is his code)
    Ill admit yes he did want id, but its simple renaming. Did he want someone to come and remake his script for him? No he didn't, and how will this video of the day be the same if he uses this code rather than your additional function. The video of the day is either probably picked by a rating or daily views or random, all of which have the potential to have the same video for multiple days and beyond that is completely irrelevant to the question he originally asked, which was how to you make it so it doesn't pick a video which doesn't exist. Yes his code is quite bad for picking 2 things per page, but if he wanted me to write the whole page for him, he would be paying me, such is not the case so I'll leave the rest the same as its working for him. Also how do you know its liable to skip back? He posted no function for his video of the day, so how can you see this with the code posted? The answer is you can't and you assumed.

    Quote Originally Posted by Czaries
    Wow... Annex, please relax instead of trying to defend yourself. That statement was not a personal attack, but rather a statement of truth. The code you posted does not provide the thread starter with a real solution to their problem. There is more code that will have to be written there to handle the "stuff you want to happen if videourl isn't set", which doesn't help him, especially when he specified that he ALWAYS wants a video to be displayed.

    Please step back and look at what I was trying to say. I don't have any problem whatsoever with the poster not using my solution. As I said in my previous post, wKkay posted an excellent solution that should be used instead of my own. In a forum like this, you need to be open to constructive criticism on anything you post. No one is trying to put you down here - we are simply trying to come up with the best solution to a problem.
    How is it a statement of truth, It isn't possible to get nothing at all, because if you got nothing it would execute the code that occurs after

    Code:
    if (!$a[videourl]) {
    I have no clue what the OP wants to do after he finds that the videourl isn't set, so i left a clearly marked place where it should go. All of you a jumping ahead of the gun and making the daily rotator for him when he only asked for a script to select a row that exists, which none of you provided. Regardless of what you considered constructive criticism, You have to give the OP what he asked for not what he didn't. If I asked how to solve 3 x 3, you better not go out and get me a bunch of flash sheets, because I don't need them and they are irrelevant to what I was doing. I didn't act defensively at all, I simply illustrated what you posted was false. If you need me to look back and pick apart what you were trying to say and the many illogical moves were made I by all means will do so, but read a post before you make a reply especially when you are saying something someone contributed is bad or won't work or won't be the best solution.
    Looking for Work level 1-3 Support Tech, Sales/billing tech, Odd management Jobs, and PHP/Mysql small jobs.
    PM me or drop me an email at mm(AT)gotannex(dot)com
    http://monitor.gotannex.com/

  24. #24
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Wow Annex, have you ever heard the expression "When you find yourself in a hole, stop digging!"?

    The OP asked for a method of only picking an id that exists in the database. You gave him a method of checking if a non-existent id has been selected - not helpful, since it leaves him still needing to choose an id that exists (exactly where he started).

    Quote Originally Posted by Annex View Post
    The video of the day is either probably picked by a rating or daily views or random, all of which have the potential to have the same video for multiple days and beyond that is completely irrelevant to the question he originally asked, which was how to you make it so it doesn't pick a video which doesn't exist.
    It's actually rather common to widen the scope of a question, particularly if there may be unwanted effects that the OP may not have foreseen. I'd call it being helpful - I hope the OP sees it the same way.

    Yes his code is quite bad for picking 2 things per page, but if he wanted me to write the whole page for him, he would be paying me, such is not the case so I'll leave the rest the same as its working for him.
    Since he's not paying you, of course you're under no obligation to help him. But if you don't want to help, why contribute to the thread at all?

    Also how do you know its liable to skip back? He posted no function for his video of the day, so how can you see this with the code posted? The answer is you can't and you assumed.
    Perhaps you should try reading the code originally posted. I took a while to see it myself, but the method by which the video of the day is chosen is clearly shown there. Then when you understand what it's doing you might think about the consequence of changing the number of videos available from 99 to 100, say, sometime towards the middle of the year.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  25. #25
    Join Date
    Apr 2003
    Location
    Melbourne, AU
    Posts
    539
    Quote Originally Posted by Annex View Post
    1. Heres what the OP asked: He wanted to find how he could pick rows that only exist.

    2. I have no clue what the OP wants to do after he finds that the videourl isn't set, so i left a clearly marked place where it should go.

    3. If I asked how to solve 3 x 3, you better not go out and get me a bunch of flash sheets, because I don't need them and they are irrelevant to what I was doing.
    The problem that you laid out in #2 is exactly what the OP is avoiding (see: #1). In other words, if #1 were to be fixed (as the OP asked), then #2 is moot.

    So it looks like you pulled off a #3?
    WK Woon
    CTO | http://www.aflexi.net - A flexible Network
    Building the next generation CDN platform - DEMO .... coming soon

  26. #26
    Join Date
    Jul 2003
    Posts
    637
    There still seems to be one minor issue with this script. The $a[videourl] field sometimes contains a special character, such as a dash. The script then as a blank output for that field. It seems it's not handling the dashes correctly. Any suggestion for this?

  27. #27
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    I don't see anything in the original code block that would change with a special character in your video url. Try to identify where the value is getting lost and if necessary post the code around there.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  28. #28
    Join Date
    Aug 2001
    Location
    Central USA
    Posts
    200
    Those dashes may actually be multi-byte Unicode characters. This happens a lot these days, especially if the content was copy/pasted in from Microsoft Word or similar word processing program. What is the collation of your MySQL database (what character set are you using)?
    InvoiceMore - Online Billing & Invoicing
    phpDataMapper - Object-Oriented PHP5 Data Mapper ORM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •