For the life of me, I cannot get this query right.

PHP Code:

$query = "SELECT category_name, subcategory_id FROM categories LEFT JOIN subcategories ON subcategories.subcategory_id = categories.category_name ORDER BY category_name, subcategory_id"; 


The url:

PHP Code:

<?php echo '<a href=categories.php?id='.$row['subcategory_id'].'>'.$category.' </a>';?>

The above query does display on the page without errors, but the objective here is to have a link with category_name (from the categories table) as the text on the page, and subcategory_id (from the subcategories table) as the link itself.

The output url is: http://reunitemysite.com/categories.php?id=

There is no id in the url. I want the url to link to the subcategory id in the subcategories table.

Using Php 5.2.17
MySQL 5.1.52

Boy, I wish they'd standardise php and mysql code once and for all, I am tired of trying to find the solution when all it is, is a minor syntax issue that's always next to impossible to find. What's the deal with that? I've tried many solutions, but none worked for me. I am sure it's probably something very simple too.

And the code that creates $row is????

You've either got a wrong query or a badly named field. Can you show the two table definitions.

Originally Posted by chrishirst

And the code that creates $row is????

I would have thought the code was already complete...Care to explain what you mean by that?

Nope, you have posted the query concatenation and the output getting written to the user agent, but the bit where the query is sent to the SQL server and a recordset is returned is missing.

Originally Posted by chrishirst

Nope, you have posted the query concatenation and the output getting written to the user agent, but the bit where the query is sent to the SQL server and a recordset is returned is missing.

Oh you mean this? I left it out of the original post, sorry.

// THIS PART

PHP Code:

$result = mysql_query($query) or die(mysql_error()); 


I had it there, it still didn't work as expected. Which was because the row $row[subcategory_id] was blank in the url. I double even triple checked the row name was spelled correctly and it exists.

Still no dice.

In the SQL query you have:

PHP Code:

LEFT JOIN subcategories ON subcategories.subcategory_id = categories.category_name 


I guess that categories.category_name is a VARCHAR (i mean, the name of this cat) and subcategories.subcategory_id is a INT. You should "connect" subcategory_id with CATEGORY_ID, not with CATEGORY_NAME

Originally Posted by hellboy124

In the SQL query you have:

PHP Code:

LEFT JOIN subcategories ON subcategories.subcategory_id = categories.category_name 


I guess that categories.category_name is a VARCHAR (i mean, the name of this cat) and subcategories.subcategory_id is a INT. You should "connect" subcategory_id with CATEGORY_ID, not with CATEGORY_NAME

After "connecting" subcategory_id with CATEGORY_ID, I am getting this error:

Column 'category_id' in field list is ambiguous

Here's what I changed to:

PHP Code:

$query = "SELECT category_id, subcategory_id FROM categories LEFT JOIN subcategories ON subcategories.subcategory_id = categories.category_id ORDER BY category_id, subcategory_id"; 


Thanks again!
-Brian

Ups i didn't realized... do you have "cat_id" in your subcat table?

How does your tables look like?

You should do it like that...

CATEGORIES
- category_id
- category_name
- ...

SUBCATEGORIES
- subcategory_id
- subcategory_cat_id
- subcategory_name
- ...

then would be the query like that:

PHP Code:

$query = "SELECT categories.category_id, subcategories.subcategory_cat_id FROM categories LEFT JOIN subcategories ON subcategories.subcategory_cat_id = categories.category_id ORDER BY categories.category_id, subcategories.subcategory_cat_id"; 


Ok,

I am going to check that right now.

Finally, got the mysql query right. Man I thought I'd never get that. One more issue though...How come the link is not making a new page? I am looking for the link to open up to a page (dynamically). The links ARE correct though that they show the correct id.

The page:
http://reunitemysite.com/categories.php

Thank you for your help Hellboy.

-Brian

PHP Code:

if ( isset($_GET['id']) && is_numeric($_GET['id'] ) {

   // Output that, what you want, when a ID is clicked

} else {

    // Output categories table

} 


Originally Posted by hellboy124

PHP Code:

if ( isset($_GET['id']) && is_numeric($_GET['id'] ) {

   // Output that, what you want, when a ID is clicked

} else {

    // Output categories table

} 


Sorry for not understanding the above, Could you put some sample in the comment sections?

I was thinking on these lines, (It was from another script online) but it didn't work all it gave me was this is where the error occurs message:

PHP Code:

//Check for a valid user ID, through GET or POST:
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) 
{ 

    

//From view_users.php

    

$id = $_GET['id'];
} 
elseif( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) 
{ 

    

// Form submission

    

$id = $_POST['id'];
} 
else 
{ 

    

// No valid ID . kill the script.

    

echo '<p class="error">This is where the error occurs.</p>';

    

//include('footer.html');

    

//exit();
} 


Thank you
Brian

Originally Posted by Brian07002

I am getting this error:

Column 'category_id' in field list is ambiguous

FYI -The "ambiguous" error means that the column name exists in more than one called table. You would have to associate the table for each column referred to such as table_name.column_name or alias.column_name

Originally Posted by mgraphic

FYI -The "ambiguous" error means that the column name exists in more than one called table. You would have to associate the table for each column referred to such as table_name.column_name or alias.column_name

That's correct.

Brian, the extra bit of code you posted has $result as the recordset variable, but your original code has $row as the recordset variable.
One of them must be wrong or there is your "problem"

I will give you a example, what i ment.

categories.php

PHP Code:

<?php

// create conection to DB
$db_link = mysql_connect("db_host", "db_user", "db_pass");
$db_name = mysql_select_db("db_name");

if ( isset($_GET['id']) && is_numeric($_GET['id'] ) {

    $cat_id = $_GET['id']

    $subcats_query_sql = "SELECT * FROM categories, subcategories WHERE categories.category_id = $cat_id AND categories.category_id = subcategories.subcategory_cat_id";

    $subcats_query = mysql_query($subcats_query_sql);

   echo "<ul>";
    while ( $row = mysql_fetch_array($subcats_query) ) echo "<li>$row['subcategory_name']";
    echo "</ul>";

    // Outputs you a list of subcategories in this category

} else {

    $cat_query_sql = "SELECT * FROM categories";

    $cat_query = mysql_query($cat_query_sql);

    echo "<ul>";
    while ( $row = mysql_fetch_array($cat_query) ) echo "<li><a href=categories.php?id=$row['category_id']>$row['category_name']</a>";
    echo "</ul>";

    // This is the categories list

} 

?>

Originally Posted by hellboy124

I will give you a example, what i ment.

categories.php

PHP Code:

<?php

// create conection to DB
$db_link = mysql_connect("db_host", "db_user", "db_pass");
$db_name = mysql_select_db("db_name");

if ( isset($_GET['id']) && is_numeric($_GET['id'] ) {

    $cat_id = $_GET['id']

    $subcats_query_sql = "SELECT * FROM categories, subcategories WHERE categories.category_id = $cat_id AND categories.category_id = subcategories.subcategory_cat_id";

    $subcats_query = mysql_query($subcats_query_sql);

   echo "<ul>";
    while ( $row = mysql_fetch_array($subcats_query) ) echo "<li>$row['subcategory_name']";
    echo "</ul>";

    // Outputs you a list of subcategories in this category

} else {

    $cat_query_sql = "SELECT * FROM categories";

    $cat_query = mysql_query($cat_query_sql);

    echo "<ul>";
    while ( $row = mysql_fetch_array($cat_query) ) echo "<li><a href=categories.php?id=$row['category_id']>$row['category_name']</a>";
    echo "</ul>";

    // This is the categories list

} 

?>

I am getting an error on this line:

PHP Code:

if ( isset($_GET['id']) && is_numeric($_GET['id'] ) { 


Parse error: syntax error, unexpected '{ in categories.php

Ps: I know it may not be relating, but I changed the db info, so that shouldn't be the problem.

You are missing the closing ) on your if statement

Originally Posted by mgraphic

You are missing the closing ) on your if statement

After adding that closing parenthesis ) to this line:

PHP Code:

if ( isset($_GET['id']) && is_numeric($_GET['id'] ) ) { 


I am getting this error:

Parse error: syntax error, unexpected T_VARIABLE in categories.php on this line:

PHP Code:

$subcats_query_sql = "SELECT * FROM categories, subcategories WHERE categories.category_id = $cat_id AND categories.category_id = subcategories.subcategory_cat_id"; 


Any ideas?

Thank you in advance.
-Brian

You are missing a ; on the line before it