help with MySQL script

gibsongk55 · September 10, 2010

Hi,

I have been working for days trying to get this to work.

$result = $db->query("SELECT id,title,approve,gold FROM dle_post WHERE approve= 1 AND gold = 1 ");
    while ($row2 = $db->get_row($result)) {
        $title_id[$row2['id']] = stripslashes($row2['title']);    
    }
    
$result = $db->query("SELECT * FROM dle_images LEFT JOIN dle_post ON dle_images.news_id = dle_post.id WHERE dle_post.approve =1 AND dle_post.gold = 1 ORDER BY RAND()  LIMIT 20");

The above code I can run each of the two MySQL statements in phpmyadmin as a SQL query and for each query I receive 4 rows. This is correct as 4 different rows contain approve=1 AND gold=1.

Now my php shows it has selected none. Is there a way to combine these two SQL statements into one? A friend that is a programmer checked it and said he is 100% sure the sql statements are not working. But why would it work in phpmyadmin and not in the php code?

MySQL version 5.1.47-community-log

PHP version 5.2.13

SELECT id,title,approve,gold FROM dle_post WHERE approve= 1 AND gold = 1

SELECT * FROM dle_images LEFT JOIN dle_post ON dle_images.news_id = dle_post.id WHERE dle_post.approve =1 AND dle_post.gold = 1 ORDER BY RAND() LIMIT 20

Appreciate any help.

Thanks,

Gibs

kickstart · September 10, 2010

Hi

You 2nd SQL statement seems to be trying to get the same data as the first as well as other data already.

One thing is that you are using a LEFT OUTER JOIN. The idea of this is that if there is a row in the left table but no matching row in the right table it will still bring back a row (just that the fields that come from the right table will be null). However you are then checking WHERE dle_post.approve =1 AND dle_post.gold = 1, which will exclude any rows which have not matched (as those 2 die_post fields would be NULL). As such you may as well use a normal inner join.

I can't see anything obviously wrong, but I am not sure what database access class you are using.

All the best

Keith

gibsongk55 · September 10, 2010

Thanks Keith. Not sure what to try either. There is an array which stores the results. I used the LEFT JOIN because the first table has less records than the 2nd.

Here is the entire script. Do you have any suggestions or testing solutions? Thanks, Gibs

<?php
function dle_img () {
    global $db;
    
    $arr_Out = array();
    $result = $db->query("SELECT id,title,approve,gold FROM dle_post WHERE approve= 1 AND gold = 1 ");
    while ($row2 = $db->get_row($result)) {
        $title_id[$row2['id']] = stripslashes($row2['title']);    
    }
    
$result = $db->query("SELECT * FROM dle_images LEFT JOIN dle_post ON dle_images.news_id = dle_post.id WHERE dle_post.approve =1 AND dle_post.gold = 1 ORDER BY RAND()  LIMIT 20");

    $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 = "..." ) next($row) {

$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>
        </td>
        </td>
        </table>
        <p><p><p>
        </a></td></tr></table>";
}
else
{
$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) {$arr_Out[] = $out; $out = '';}
if($i == 10) {$arr_Out[1] = $out; $out = '';}
        $i++;
}
}
    return $arr_Out;
}

$dle_img = dle_img();



?>

kickstart · September 10, 2010

Hi

Simplifying that code what you have is:-

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

	$arr_Out = array();

	$result = $db->query("SELECT a.images, a.news_id b.id,b.title,b.approve,b.gold FROM dle_images a INNER JOIN dle_post b ON dle_images.news_id = dle_post.id WHERE dle_post.approve =1 AND dle_post.gold = 1 ORDER BY RAND()  LIMIT 20");

	$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];

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

			if (empty($row['news_id'])) 
			{
				$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>
				</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";}
				$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) {$arr_Out[] = $out; $out = '';}
				if($i == 10) {$arr_Out[1] = $out; $out = '';}
				$i++;
			}
		}
	}
	return $arr_Out;
}
$dle_img = dle_img();
?>

Note that are not going to get any records from dle_images where there isn't a matching record on dle_post with approve and gold of 1.

Also the function returns the array $arr_Out. This will only contain 0, 1 or 2 elements, and needs to have 5~9 matches to output 1 and 10 or more to output 2.

All the best

Keith

gibsongk55 · September 10, 2010

Hi Keith,

I appreciate your help and time. I don't quite understand the a and b part of the code. I assume it is the 1st table dle_post and the 2nd table dle_images.

Anyway I ran the statement:

SELECT a.images, a.news_id b.id,b.title,b.approve,b.gold FROM dle_images a INNER JOIN dle_post b ON dle_images.news_id = dle_post.id WHERE dle_post.approve =1 AND dle_post.gold = 1 ORDER BY RAND() LIMIT 20

In the SQL query in phpmyadmin and I received this error:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id,b.title,b.approve,b.gold FROM dle_images a INNER JOIN dle_post b ON dle_imag' at line 1"

I am still confused.

Thanks,

Gibs

kickstart · September 10, 2010

Hi

The a and b are just alias names for the tables. Using full table names as prefixes on the column names can get a bit long.

As to the error, I missed out a comma by the looks of it:-

SELECT a.images, a.news_id, b.id, b.title, b.approve, b.gold 
FROM dle_images a INNER JOIN dle_post b 
ON a.news_id = b.id 
WHERE b.approve =1 
AND b.gold = 1 
ORDER BY RAND()  LIMIT 20

All the best

Keith

gibsongk55 · September 11, 2010

Hi Keith,

Okay I ran that query in myphpadmin and received 4 rows... correct! However with the script, instead of the 4 images loading on the left and right side of the page I get this:

php function dle_img () { global $db; $arr_Out = array(); $result = $db->query("SELECT a.images, a.news_id, b.id, b.title, b.approve, b.gold FROM dle_images a INNER JOIN dle_post b ON a.news_id = b.id WHERE b.approve =1 AND b.gold = 1 ORDER BY RAND() LIMIT 20"); $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]; $title = substr(stripslashes($row['title']),0,25)."..."; if (empty($row['news_id'])) { $out .= "
{$title}

"; } else { if (file_exists(ROOT_DIR."/uploads/posts/".$images)) {$file_dir = "posts";} else {$file_dir = "files";} $out .= "

{$title}

[\"{$title}\"]

"; } 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(); ?>

Note that the code before did display correctly only that it was showing all records (max 10 places on the page) because it never checked for gold=1.

Thanks,

Gibs

kickstart · September 13, 2010

Hi

That suggests that the script isn't running but is just being output. Is php running on the server?

All the best

Keith

gibsongk55 · September 15, 2010

Hi Keith,

I am really close with fixing this. The MySQL part works now. Only problem with the result is that I have 9 records as a query result. The site will only show 8 at any time. Of course on refresh the 9th will show but still only a total of 8 appear.

Appreciate any help.

Gibs

<?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");

    $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\">
        <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=\"/uploads/posts/clear.jpg\" width=\"150\" height=\"95\"></td>
        </td>
        </td>
        </table>
        <p><p><p>
        </a></td></tr></table>";
}
else
{
$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) {$arr_Out[] = $out; $out = '';}
if($i == 10) {$arr_Out[1] = $out; $out = '';}
        $i++;
}
}
    return $arr_Out;
}

$dle_img = dle_img();



?>

kickstart · September 15, 2010

Hi

Can't see anything there that limits output to 8, although you do only seem to output either 5 or 10 records.

Changing it to remove the unnecessary extra SQL and to return any multiple of 5 :-

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

$arr_Out = array();

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

$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 ( $row['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=\"/uploads/posts/clear.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++;
		}
	}
}
return $arr_Out;
}

$dle_img = dle_img();

?>

If you want multiples of 5 with the last records added as well

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

$arr_Out = array();

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

$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 ( $row['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=\"/uploads/posts/clear.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();

?>

All the best

Keith

Sign In

help with MySQL script

Recommended Posts

gibsongk55

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

gibsongk55

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

gibsongk55

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

gibsongk55

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

gibsongk55

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information