matching fields from tables

[hellonoko]

I have two tables in my database: categories and urls

 

categories has the fields ID and CATEGORY where ID is a unique identifier.

 

urls has the fields ID CATEGORY URL and DESCRIPTION where ID is a unique identifier and CATEGORY is the the identifier from the categories table.

 

I am not sure how to write a script so that when I list all the entries in URLS i can display the matching label for the category ID rather than the #.

 

Thanks

Ian

[spfoonnewb]

PHP Codes:

 

<?php

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());

$result = mysql_query("SELECT * FROM urls")or die(mysql_error());  

while($row = mysql_fetch_array( $result )) {

	$url = $row['urls'];
	$id = $row['id'];


$result2 = mysql_query("SELECT name FROM categories WHERE id = '$id'")or die(mysql_error());

while($row2 = mysql_fetch_array( $result2 )) {

		echo $row2['name'];
		echo ":<br />";
		echo $url;
		echo "<p />";

	}
}
?>

 

Table Structure:

 

-- 
-- Table structure for table `categories`
-- 

CREATE TABLE `categories` (
  `id` text NOT NULL,
  `name` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES ('1', 'Google');
INSERT INTO `categories` VALUES ('2', 'Yahoo');

-- --------------------------------------------------------

-- 
-- Table structure for table `urls`
-- 

CREATE TABLE `urls` (
  `urls` text NOT NULL,
  `id` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `urls`
-- 

INSERT INTO `urls` VALUES ('http://www.google.com', '1');
INSERT INTO `urls` VALUES ('http://www.yahoo.com', '2');

 

Output:

 

Google:
http://www.google.com

Yahoo:
http://www.yahoo.com

[corbin]

See 2 posts down.

[spfoonnewb]

**Ignore**

[corbin]

<?php

mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

$q = mysql_query("SELECT * FROM urls, categories WHERE categories.id = urls.id") or die(mysql_error());
while($r = mysql_fetch_assoc($q)) {
echo "{$r['name']}:<br />\n{$r['urls']} <br />\n";
}
?>

Had a preemptive and...

[spfoonnewb]

Ya that works, never seen an SQL statement that way.. always learning

[hellonoko]

well i couldnt see how the first one works and i couldn't get the second one to work.

 

here is what i am trying to do. but it only works on the first time through the loop for some reason.

 

and im sure this is really bad way to do this.

 

<?php

include 'dbconnect.php';

$query = "SELECT * FROM urls";
$result = mysql_query($query);
$rows = mysql_num_rows($result); 	

$c_query = "SELECT * FROM categories";
$c_result = mysql_query($c_query);
$c_rows = mysql_num_rows($c_result); 	



for ($i=0; $i <$rows; $i++)
{

	$row = mysql_fetch_array($result);
	//echo $category_result;

	//$id_from_url_info = 10;

	for ($c=0; $c <$c_rows; $c++)
	{
		$c_row = mysql_fetch_array($c_result);

		if ( $row[category] == $c_row[id])
		{
			echo $c_row[category];
		}

	}

	echo	$row[category];
	echo	"<br>";
	echo	$row[url];
	echo	"<br>";
	echo	$row[description];
	echo	"<br><br>";	

}

?>

[anatak]

am I right in thinking that you want to display the data like this ?

 

google: www.google.com

yahoo: www.yahoo.com

 

?

 

if this is what you want do something like this.

 

 

$sql="SELECT * FROM urls, categories WHERE categories.id = urls.id"

$result = mysql_query($sql);

 

while($row = mysql_fetch_array($result )){

echo $row['name'].": ".$row['urls'];

 

}

 

Is this what you are looking for ?

 

anatak

[hellonoko]

That looks like what I want but it only returns :::::::

I also needed to turn then query into this:

 

	$sql="SELECT * FROM urls, categories WHERE categories.id = urls.category";

 

But that returned the same thing.

 

Basically there is a list of URLS and their information in one table for example:

 

ID: 9

URL: www.phpfreaks.net

category: 4

desription: a useful site

 

when I run through the list of entries in the URL table and i reach the CATEGORY field (4)

 

I want to cross reference to the CATEGORIES table where the entries look like.

 

ID: 4

Category: coding websites

 

So that instead of displaying 4 as the category for www.phpfreaks.net my code looks up the name for category 4 is and displays.. coding websites

 

This way later on if i want to charge the category name i can.

 

Does that explain it clearly?

 

Thanks,

Ian