Outputting data

[mochden]

Hi, im looking for help on how to get data out from a table in mysql, i gt this so far:

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM jos_seyret_items";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>What's Hot</center></b><br><br>";

$i=0;
while ($i < $num) {

$title=mysql_result($result,$i,"title");
$itemcomment=mysql_result($result,$i,"itemcomment");
$picturelink=mysql_result($result,$i,"picturelink");
$videourl=mysql_result($result,$i,"videourl");

echo "<table border="0" width="100%"><tr><td><a href="$videourl" target="_top"><img src="$picturelink" border="0" height="80" width="65"></a></td><td>$title<br>$itemcomment</td></tr></table>";

$i++;
}

?>

now inside that table jos_seyret_items i have categories for this videos, and the category field is call "catid" and has id 44 and catid*44*#, so my question is how can i pull out just the items that are under this category? im thinking is by using a $extraquerystring or something like that? im kind of new to php so any help will be awesome thanks.

[PHP_Chimp]

At the moment you are selecting everything with your $query
$query="SELECT * FROM jos_seyret_items";

If you want specific results you could go for something like
$query="SELECT * FROM jos_seyret_items WHERE catid='".$catid."';";

You may want to change to mysql_fetch_array [uk3.php.net] as opposed to the mysql_result you are using for the reason from the manual below -

When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result(). Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument.

Also in general you dont want to be using the error suppression @, as you are handling the can not connect to database error anyway...so why suppress an error? It makes it impossible to test your sctipt if you are hiding errors, so personally I would remove all of the @'s and only put them back in as a last resort in the finished version.

[mochden]

great thanks for the help, im trying to add the ORDER BY last DESC to it and is not letting me do it, that need to go after the WHERE statment right?

[PHP_Chimp]

SELECT [ ALL ¦ DISTINCT [ ON ( expression [, ...] ) ] ]
* ¦ expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION ¦ INTERSECT ¦ EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC ¦ DESC ¦ USING operator ] [, ...] ]
[ LIMIT { count ¦ ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

where from_item can be one of:

[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] ¦ column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item [ ON join_condition ¦ USING ( join_column [, ...] ) ]

This is from postgresql, but im assuming mysql will be similar enough.
So yes the ORDER BY comes after the WHERE.
If you are just after the order in reverse then just use ORDER BY <column name> DESC. Again im assuming mysql mirrors postgresql

[mochden]

$query="SELECT * FROM jos_seyret_items WHERE catid='*44*#' ORDER BY last DESC";

[edited by: eelixduppy at 12:13 am (utc) on Nov. 3, 2007]
[edit reason] tidying up [/edit]