Jump to content

help with MySQL script

gibsongk55

By gibsongk55
in MySQL Help

 Share

Recommended Posts

gibsongk55 Newbie

gibsongk55

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/213033-help-with-mysql-script/
Share on other sites
kickstart Member

kickstart

Posted
Posted

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 Newbie

gibsongk55

Posted
  • Author
Posted

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 Member

kickstart

Posted
Posted

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 Newbie

gibsongk55

Posted
  • Author
Posted

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 Member

kickstart

Posted
Posted

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 Newbie

gibsongk55

Posted
  • Author
Posted

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

 

gibsongk55 Newbie

gibsongk55

Posted
  • Author
Posted

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 Member

kickstart

Posted
Posted

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.