Simple mysql/php problem

Chronicle

Chronicle

Chronicle

Soldato
Joined
4 Jul 2004
Posts
2,647
Location
aberdeen
Hello
Think this is simple, but can't work out a simple way to do it..

Not sure how to describe it either, so i'll try my best.

I have a list of items drawn from a mysql db table (items), each with their own category.

I want to make one request for listing the items (table: items), which includes a row called "catid". And also, one request for categories (which contains the category id, and category name), save it in an array, and then when listing the items, do something along the lines of replacing the catid (from the items list), with the category name (from the categories info table).

sorry if this isn't clear
thanks
 
I think you just want to do some thing like

Code: 
SELECT items.item, category.category_name FROM items, category WHERE items.catid=category.catid
 
Thanks, but i'm not quite sure if that would help (although your probly right)

This is what i have at the moment (changed slightly just to get rid of the bits that are not important here)
PHP: 
$query="SELECT * FROM items ORDER BY id DESC LIMIT 200";
$result=mysql_query($query);
$num=mysql_numrows($result);

$mysql_counter=0;
while ($mysql_counter < $num) {
	$id=mysql_result($result,$mysql_counter,"id");
	$title=mysql_result($result,$mysql_counter,"title");
	$authorid=mysql_result($result,$mysql_counter,"authorid");
	$intro=mysql_result($result,$mysql_counter,"intro");
  $catid=mysql_result($result,$mysql_counter,"catid");

	echo "<h1><a href=\"page.php?id=$id\">$title</a></h1>";

/** NEED TO GET THE CATEGORY NAME (from 'categories' table, 'name'
 row), and put it as $category, here, but dont want to do a mysql request
 for each time this loops round, asthere could be upto 200. theres only 5
 or 6 categories , so want to put it in an array or something but im not
 sure how 
so at the moment it would do something like:

select name from categories where id = $catid

etc.

 but that can do upto an additioanl 200 requests from
 the mysql db.

**/


	echo "in category: $category";

	echo "<p>$intro<br />";
	echo "Written by author: $authorid. <a href=\"page.php?id=$id\">see more</a></b>";


$mysql_counter++;
}
thanks
 
In SQL parlance, your items table relates to your categories table. It does this by way of a "foreign key" in the items table - in this case, the catid field - which relates to a field in the other table - in this case, I presume the categories table has an field called id.

So, we need to join the results from the two tables into one result, and the SQL command for that is imaginatively called JOIN. Here's what your query would look like:

Code: 
SELECT
    categories.name, items.name
FROM
    items
INNER JOIN 
    categories ON categories.id = items.catid

It's pretty self-explanatory, but I'll go over some of the not-so-obvious bits. INNER JOINs are the most commonly used joins you'll use with SQL. With an inner join, you'll only get results if the joined table has rows matching the ON clause - in this case, where the category id matches the item's catid. If you want to return results regardless of whether there are rows in the table on the "right" hand side of the join, then you should use a LEFT OUTER JOIN.

As a point of interest, mySQL (and I think other RDBMSs) offers a shorthand INNER JOIN syntax:

Code: 
SELECT
    categories.name, items.name
FROM
    items, categories
WHERE
    categories.id = items.catid

HTH :)
 
Thanks. I'll try that later, havn't had a chance to propperly try it out. :)

On a related matter though, i'm trying to export a mysql db from a windows machine running mysql to a linux machine.

It never works ... Here is a print screen of me trying to import it, into the linux machine (using phpmyadmin). Any ideas? I dont have a clue what to do...

ffs1bh.png


Thanks

BTW:
ON LINUX MACHINE (TRYING TO IMPORT IT INTO):
PHP: 4.3.10
MYSQL: 4.0.25-standard

ON WINDOWS MACHINE (EXPORTING IT FROM THERE):
PHP: PHP Version 5.0.3
MYSQL: 4.1.7
 
Change the query to:

Code: 
CREATE TABLE `categories` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(60) NOT NULL DEFAULT '',
    `items` INT(4) NOT NULL DEFAULT '0',
    `description` VARCHAR(60) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE = MYISAM DEFAULT CHARSET = latin1
 
Ok thanks.
icon14.gif
Is there a way for phpmyadmin (v 2.6.1) to remove those bits, in one of the options (had a look can't see anything but meh i'm crap with it)? Got quite a few tables, and expect to be exporting quite a bit this week (testing stuff out).

Thanks again
 
You must log in or register to reply here.
Back
Top Bottom