Databases
StackOverflow2013
Postsdbo
PORandom SQL record while excluding specific records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORandom SQL record while excluding specific records
    primarykey
    data
    text
    <p>I have a CodeIgniter PHP application that shows two movie covers. Beside them is a "random movie" button that uses AJAX to replace the two movies with a new set of movies. You can continue to click this, over and over, and see it continue to replace the images of the movie covers. The first two covers to show are set as the defaults, and they should never show after the user has clicked the random movie button. The problem is this: <strong>When clicking the random movie button, it will some times take many clicks to finally show a new cover. That is, the same cover will be returned multiple times in a row.</strong> The two different covers being fetched are being called from slightly different URLs, so they will rarely both break at the same time. This lets me know that it is refreshing, but that the function is returning the same movie multiple times. If I access the url that is being called via AJAX directly, I never see this take place since I have used the Session class to store the last movie's and exclude it from the SQL query (i.e. <code>WHERE id NOT IN ($default_movie, $last_movie)</code>). <strong>Any idea why accessing the url directly would work fine, but when calling via AJAX, I'm seeing this behavior?</strong> </p> <p>I know this may not have been as clear as possible, so let me know if I can clarify something that doesn't make sense. I'll add code if that helps as well. Thanks friends! </p> <p><strong>Query to get random movie:</strong></p> <pre><code>SELECT * FROM (`movies`) WHERE `id` NOT IN (2, 10) ORDER BY RAND() LIMIT 1 </code></pre> <p><strong>Model method:</strong></p> <pre><code>public function getRandom($count = 1, $featured = FALSE, $series = FALSE, $exclude = 0, $last = 0) { $this-&gt;db-&gt;order_by('id', 'random'); $this-&gt;db-&gt;limit(1); $conditions = array(); if ($exclude &gt; 0) { $conditions['id !='] = $exclude; } if ($last &gt; 0) { if (!empty($conditions['id !='])) { $conditionsNotIn = "id NOT IN (" . $conditions['id !=']. ", $last)"; unset($conditions['id !=']); $this-&gt;db-&gt;where($conditionsNotIn); } else { $conditions['id !='] = $last; } } if ($featured) { $conditions['featured'] = 1; } if ($series) { $conditions['current_series'] = 1; } $movie = $this-&gt;db-&gt;get_where('movies', $conditions); $movie = $movie-&gt;row(); if (!is_null($movie)) { return $movie; } else { return FALSE; } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJamieHoward
    1. USJamieHoward
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORandom SQL record while excluding specific records
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORandom SQL record while excluding specific records
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PORandom SQL record while excluding specific records
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you post your full SQL query
      singulars
      1. PORandom SQL record while excluding specific records
      1. USBrett Gregson
    2. COHave you verified that the AJAX requests are properly populating the `$default_movie` and `$last_movie` variables?
      singulars
      1. PORandom SQL record while excluding specific records
      1. USeggyal
    3. COYes, I have added the query. I also was utilizing CI's log_message() to track that the variables were changing every time.
      singulars
      1. PORandom SQL record while excluding specific records
      1. USJamieHoward
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload