help please my MySQL random INNER JOIN

[gibsongk55]

Hi,

 

I had problems with this script before and thought it was fixed but it still does not work correctly.  What this should do is find records in the database that match the criteria of gold=1 AND approve=1

 

The results should be taken randomly if in case there are more than 10 results.

 

There are 10 places on the main page of the site to show an image for these results.  If there are more than 10 records that meet the criteria it should randomly grab 10.  If less than 10 records I have it display the image "gold.jpg" instead which just displays a seal that says advertise here. 

 

Currently the database has 10 records that meet the criteria but it is displaying about 5 seals when there should only be 1.

 

Here is the code.

 

 

<?php
function dle_img () {
global $db;

$arr_Out = array();
$result = $db->query("SELECT id,title,approve FROM dle_post WHERE gold=1 AND approve=1");
while ($row2 = $db->get_row($result)) {
$title_id[$row2['id']] = stripslashes($row2['title']);
}

// $result = $db->query("SELECT * FROM dle_images INNER JOIN dle_post ON dle_images.news_id = dle_post.id ORDER BY RAND() LIMIT 20");
$result = $db->query("SELECT * FROM dle_images INNER JOIN dle_post ON dle_images.news_id = dle_post.id ORDER BY RAND() ");


$imgar = array ();
$i = 1;
while ( $row = $db->get_row($result)) {
$images = $row['images'];
if (in_array($images, $imgar)) continue;
$imgar[] = $row['images'];
$images = explode("|||",$images);
$images = $images[0];
if (file_exists(ROOT_DIR."/uploads/posts/".$images)) {$file_dir = "posts";}
else {$file_dir = "files";}

$title = substr($title_id[$row['news_id']],0,25)."...";

// if (empty($row['news_id'])) {
if ( $title == "...") {

$out .= "<table align=\"center\"><tr><td align=\"center\">
<img border=\"0\" src=\"http://www.sbowlrentals.com/uploads/posts/gold.jpg\" width=\"150\" height=\"95\"></td>
</td>
</a></td></tr></table>";
}
else
{
$out .= "<table align=\"center\"><tr><td align=\"center\">
<a href=\"/index.php?newsid={$row['news_id']}\">
<img border=\"0\" src=\"/uploads/".$file_dir."/{$images}\" width=\"150\" height=\"95\" title=\"{$title}\"></td>
</td>
</table>
<p><p><p>
</a></td></tr></table>";
}

if($out)
{
if($i == 5) {$arr_Out[] = $out; $out = '';}
if($i == 10) {$arr_Out[1] = $out; $out = '';}
$i++;
}
}
return $arr_Out;
}

$dle_img = dle_img();
?>

 

Appreciate any help.  You can see site here --->  http://www.rent4bowls.com

 

Gibs

[kickstart]

Hi

 

Bit uncertain exactly what you want to get out.

 

However a brief look at it and the first bit of SQL seems unnecessary as you are getting the same field back in the 2nd piece of sql.

 

Also there is no code to limit the number of times it puts out gold.jpg while your post suggests you only want it once. If so probably best to just set a flag when you put it out the first time and refuse to after that.

 

Also your code will only ever put out 6 or 11 records ($i starts at 0 and is incremented after the check).

 

All th ebest

 

Keith

[gibsongk55]

Hi Keith,

 

I appreciate your input.  Not sure where to go from here. 

 

Okay this is what I need to happen.

 

If FROM dle_post (table) approve=1 AND gold=1 then I need an image for that record which is JOINED from another table dle_images to display on the main page.

 

There are 10 places on the main page to display these records.  If there are more than 10 records in the database meeting this criteria, then I need it to randomly choose 10 to fill all spots on the main page.

 

Currently I am only showing a total of 4, 5, 6, or  7 selected records on the main page when in fact there are 9 records that meet the criteria approve=1 AND gold=1.

 

I don't understand arrays at all as this was programmed for me.  Appreciate any help you can give.

 

Regards,

 

Gibs

[gibsongk55]

Hi Keith,

 

I went back to an old post you helped me before with and worked on some code I couldn't get to work before.  I actually got it to show the 9 records correctly that meet the criteria.

 

Only thing is that is does not display the title line above the image and it does not show the gold.jpg for the 10th spot since only 9 out of 10 records are found.

 

I had to change the LEFT OUTER JOIN to INNER JOIN and select from dle_post first instead of dle_images because the post table has the fields to check if approve=1 and gold=1, otherwise all the image files in the dle_image table will have invalid records.

 

I know this piece of code is missing:

 

        $title_id[$row2['id']] = stripslashes($row2['title']);

 

which in the new code there is now $row2, so i changed it to:          $title_id[$row['id']] = stripslashes($row['title']);

but I don't know where to place it without getting an error.

 

Here is what I have so far:

 

<?php
function dle_img () 
{

global $db;

$arr_Out = array();

$result = $db->query("SELECT * FROM dle_post INNER JOIN dle_images ON dle_post.id = dle_images.news_id AND dle_post.gold=1 AND dle_post.approve=1 ORDER BY RAND()  LIMIT 10");

$imgar = array ();
$i = 1;
while (  $row = $db->get_row($result)) 

{

$images = $row['images'];

if (!in_array($images, $imgar))
{

$imgar[] = $row['images'];
$images = explode("|||",$images);
$images = $images[0];

       
// if (empty($row['news_id'])) {
if ( $title == "...") 
{

$out .= "<table align=\"center\"><tr><td align=\"center\">
        <table cellpadding=\"0\" cellspacing=\"0\" style=\"margin-right:10px\" height=\"135\">
        <tr>
        <font face=\"arial\" size=\"1\" color=\"#000000\"></font></center></b></td>
        </tr>
        <td style=\"background-color:transparent\">
        <div style=\"height:2px\"></div>
        <img border=\"0\" src=\"http://www.rent4bowls.com/uploads/posts/gold.jpg\" width=\"150\" height=\"95\"></td>
        </td>
        </td>
        </table>
        <p><p><p>
        </a></td></tr></table>";
}
else
{
if (file_exists(ROOT_DIR."/uploads/posts/".$images)) {$file_dir = "posts";}
else {$file_dir = "files";}
substr($row['title'],0,25)."...";}
$out .= "<table align=\"center\"><tr><td align=\"center\">
        <table cellpadding=\"0\" cellspacing=\"0\" style=\"margin-right:10px\" height=\"135\">
        <tr>
        <font face=\"arial\" size=\"1\" color=\"#000000\">{$title}</font></center></b></td>
        </tr>
        <td style=\"background-color:#000000\">
        <div style=\"height:2px\"></div>
        <a href=\"/index.php?newsid={$row['news_id']}\">
        <img border=\"0\" src=\"/uploads/".$file_dir."/{$images}\" width=\"150\" height=\"95\" title=\"{$title}\"></td>
        </td>
        </table>
        <p><p><p>
        </a></td></tr></table>";
}
if($out)
{
if(($i % 5) == 0) {$arr_Out[] = $out; $out = '';}
$i++;
}
}
if ($out != '')
{
$arr_Out[] = $out;
}
return $arr_Out;
}
$dle_img = dle_img();
?> 

 

Thanks again for your help.

 

Gibs