SQL is being stupid

[Josh]

[font=\"Trebuchet Ms\"]

Hello,
If anyone could help me or something it would be greatly appreciated. I\'ve been getting help from the penguin (ner0) and we\'ve come up with this code and it\'s not working the way I want it and at the moment I can\'t get ahold of paul. So here goes the explaination of what it should be doing.

This code *should* display 3 of the latest entries from the database inside of a table. This will work for every category I have submitted into the database (The category is submitted inside the table the news is inserted into). I hope this all makes sense. Here is the code.

Code: Select all

<?php

$sql_host = "localhost";
$sql_user = "*";
$sql_pass = "**";
$sql_db = "news";

$conn = @mysql_connect&#40;$sql_host, $sql_user, $sql_pass&#41; or die&#40;"could not connect. mysql said&#58; <br /><br />" . mysql_error&#40;&#41;&#41;;
$select = mysql_select_db&#40;$sql_db&#41; or die&#40;"cannot select database. mysql said&#58;<br /><br />" . mysql_error&#40;&#41;&#41;;


	$query = mysql_query&#40;"select * from &#96;news&#96; order by &#96;cat&#96; asc"&#41;;
	$table = array&#40;&#41;;
	while &#40;$temp=mysql_fetch_array&#40;$query&#41;&#41;
	{
		$table&#91;$temp&#91;\'cat\'&#93;&#93;&#91;&#93; = $temp&#91;\'id\'&#93;;
	}
	echo "<table border=1px>&#092;n";
	echo "	<tr>&#092;n";
	// how many columns per row?
 	$col = 2;
 	$i = 0;
 	foreach&#40;$table as $key => $val&#41;
 	{


	if &#40;$i === $col&#41;
	{
		echo "	</tr>&#092;n";
		echo "	<tr>&#092;n";
		$i = &#40;$i - 2&#41;;
	}
		echo "		<td>&#092;n";
 		echo "			<b>category&#58;</b>{$key}<br />&#092;n";

			//------------------
			// for each id in this category...
			//------------------
foreach &#40;$val as $subval&#41;
  {

   //------------
   // fetch details based on the current id &#40;we\'re looping thru\' \'em&#41;
   //------------

			   $query = mysql_query&#40;"select * from &#96;news&#96; where id=\'$subval\' limit 3"&#41;;
			   $temp = mysql_fetch_array&#40;$query&#41;;

   //------------
   // output details&#58;
   //------------

			   echo "		   <i>title&#58;</i> {$temp&#91;\'title\'&#93;} <br />&#092;n";
  }
		echo "		</td>&#092;n";
		$i++;
 	}
 echo "</table>&#092;n";

Notice how I\'m using a query near the bottom of the script where it should LIMIT 3, but it isn\'t.

Thanks in advanced,
-Dren <img src=\'http://www.killanet.net/forum3/public/s ... ictory.gif\' class=\'bbc_emoticon\' alt=\':victory:\' />


[/font][/size]

[Josh]

Okay update... I fixed it so here is the new code. It just isn\'t doing what I want now..... aka display the category in a cell and a list of the three latest news entries under it... :&#092;

Code: Select all

<?php

$sql_host = "*";
$sql_user = "**";
$sql_pass = "";
$sql_db = "news";

$conn = @mysql_connect&#40;$sql_host, $sql_user, $sql_pass&#41; or die&#40;"could not connect. mysql said&#58; <br /><br />" . mysql_error&#40;&#41;&#41;;
$select = mysql_select_db&#40;$sql_db&#41; or die&#40;"cannot select database. mysql said&#58;<br /><br />" . mysql_error&#40;&#41;&#41;;

	$query2 = mysql_query&#40;"select * from &#96;news&#96; limit 3"&#41;;
	$query = mysql_query&#40;"select * from &#96;news&#96; order by &#96;cat&#96; asc"&#41;;
	$table = array&#40;&#41;;
	while &#40;$temp=mysql_fetch_array&#40;$query&#41;&#41;
	{
		$table&#91;$temp&#91;\'cat\'&#93;&#93;&#91;&#93; = $temp&#91;\'id\'&#93;;

	}

	echo "<table border=1px>&#092;n";
	echo "	<tr>&#092;n";

	// how many columns per row?
 	$col = 2;
 	$i = 0;
 	foreach&#40;$table as $key => $val&#41;
 	{
		if &#40;$i === $col&#41;
		{
			echo "	</tr>&#092;n";
			echo "	<tr>&#092;n";
			$i = &#40;$i - 2&#41;;
		}

		echo "		<td>&#092;n";
 		echo "			<b>category&#58;</b>{$key}<br />&#092;n";

		//------------------
		// for each id in this category...
		//------------------
		foreach &#40;$val as $subval&#41;
		{
			//------------
			// fetch details based on the current id &#40;we\'re looping thru\' \'em&#41;
			//------------

			//$query2 = mysql_query&#40;"select * from &#96;news&#96; where id=\'$subval\' limit 3"&#41;;
			$temp = mysql_fetch_array&#40;$query2&#41;;

			//------------
			// output details&#58;
			//------------
			 if &#40;$temp&#91;\'title\'&#93;&#41; {

				echo "		  >>> {$temp&#91;\'title\'&#93;} <br />&#092;n";
			}
		}

		echo "		</td>&#092;n";
		$i++;
	}

	echo "</table>&#092;n";

[radar]

So it's a basic news script?

Code: Select all

<?
&#036;sql_host = "*";
&#036;sql_user = "**";
&#036;sql_pass = "";
&#036;sql_db = "news";
//assuming your table is called news also...
&#036;q = mysql_query&#40;"SELECT * FROM &#96;news&#96; ORDER BY &#96;id&#96; DESC LIMIT 0 , 3"&#41;;
/*The line selects all fields from news and then orders them by ID. Basically, if you have an auto-numerical
system for your IDs, then the highest ID is going to be the first post shown. Lets say you have a news post
titled "post one" with the ID of 84 and another with the ID of 83 titled "post two". Post one will be shown
before post two*/
while &#40;&#036;a = mysql_fetch_array&#40;&#036;q&#41;&#41; {
echo "insert table or whatever here";
//variables are &#036;a&#91;fieldname&#93;
}

?>

[color=\"green\"]edit the vercz way: fixed tags[/color]

[Josh]

Yes it\'s a news script, but you also have to grab the category which is with the news... So I have to grab the category and make sure the news is sorted into the right category... And it\'s not doing that now. It used to, but it doesn\'t now.

[Scott]

I\'m a bit confused as what your trying to get the code to do. Can you paste an example of the table your querying? Particularly confused about the use of ID. Is it different for every entry, or different for every category? Can a category have more than one news report in it?

Em..I think thats all <img src=\'http://www.killanet.net/forum3/public/s ... /smile.gif\' class=\'bbc_emoticon\' alt=\':)\' />

[Josh]

lol yanno... I\'m just as confused as you <img src=\'http://www.killanet.net/forum3/public/s ... >/blum.gif\' class=\'bbc_emoticon\' alt=\':P\' /> How about I just start over. <img src=\'http://www.killanet.net/forum3/public/s ... iggrin.gif\' class=\'bbc_emoticon\' alt=\':D\' /> yes... we\'ll do that. No multi deminsional arrays or whatever. <img src=\'http://www.killanet.net/forum3/public/s ... >/blum.gif\' class=\'bbc_emoticon\' alt=\':P\' />

[radar]

You want it ordered by the category and the ID? Easy.

&#036;q = mysql_query("SELECT * FROM &#96;news&#96; ORDER BY &#96;catid&#96; DESC AND ORDER BY &#96;id&#96; DESC");

Not quite sure if it will work or not.

[Josh]

Hmmmm... You just gave me an idea radar, thanks <img src=\'http://www.killanet.net/forum3/public/s ... >/blum.gif\' class=\'bbc_emoticon\' alt=\':P\' />