placing two mysql tables in a query

[oraya]

How would I write the query to pull all from the table useful_links and category_name from the table categories?

 

Sorry a real newbie, any help would be wonderful.  Thank you in advance and have a great Wednesday.

 

Oraya

[HDFilmMaker2112]

Use the table name with a dot after it, then the column you want to pull. * to pull all columns.

 

You need a column in one table that has the same data in a column in the other table to link on.

So table 1 column 1 has an ID, you'd need the same ID number in the second table to link the two tables.

 

SELECT useful_links.*, categories.category_name FROM useful_links JOIN categories ON (need to know what column matches to write the ON portion) WHERE (add your WHERE condition here.)

 

Could you give an example of your table structure?

[oraya]

Ah no colums have the same name other than id.  Is it important for them to match?

[oraya]

<?php
$id = mysql_real_escape_string( $_GET["id"] );
$title = 'And New Useful Link';
$table = 'useful_links';
include_once'inc/_layout-header.php';
include_once'includes/_dbconnect.php';
// Retrieve data from database
$query="SELECT category_name FROM categories";

$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();


$category=mysql_result($result,$i,"category");
$category_name=mysql_result($result,$i,"category_name");
$link_title=mysql_result($result,$i,"link_title");
$link_address=mysql_result($result,$i,"link_address");
$description=mysql_result($result,$i,"description");
?>


<fieldset>
<legend class="formtitle">Update - <?php echo $title;?></legend>

<form action="includes/submit.php" method="post">

<input type="hidden" name="table" value="<?php echo $table;?>" />

<input type="hidden" name="id" value="<?php echo $id;?>" />

<p class="form"><label class="formlabel">Category</label> 
<select name="category">
    <optgroup label="Categories">
<?php
$i=0;
while ($i < $num) {
?>  
<option><?php echo $category_name; ?></option>
<?php
$i++;
}
?></p>

<p class="form"><label class="formlabel">Link Title </label> <input type="text" name="link_title" value="<?php echo $link_title;?>" size="30" class="updatesubtitle" /></p>

<p class="form"><label class="formlabel">Link Address </label> <input type="text" name="link_address" value="<?php echo $link_address;?>" size="30" class="updatesubtitle" /></p>

<p class="form"><label class="formlabel">Description</label> <textarea name="description" class="widgEditor nothing" rows="10" style="width: 100%" wrap="virtual"><?php echo $description;?></textarea></p>

<input type="submit" value=" Update Content " id="submit" />

</form>
</fieldset>

<?php
include_once'inc/_layout-footer.php'; ?>

 

This is my page, I'm just not sure how to pull the category_name from categories and all from useful_links

[oraya]

Could someone explain to me how I do what  HDFilmMaker2112 suggested.  At present the two tables are look like this:

 

Categories

 

| id | category_name |

 

 

 

useful_links

 

| id | category | link_title | link_address | description |

 

 

I don't mind changing the table names or field names if I have to, but if someone could just explain to me how it works and what I need to do I'd be very grateful.  The reason I put the categories in a separate table was that I didn't want everyone just making up loads of categories.  This way I was able to set them for new adds through the use of a drop down.  But now on the update form I'd like to be able to offer the option to change categories.

 

Many thanks in advance,

Oraya

[HDFilmMaker2112]

The column names don't have to match, but the data does.

 

I know this won't be exactly what you need, but it's an example:

 

Say I have two tables

 

categories:

id | category | description
1  | example | example description

 

products:

product_id | product | product_price | category_id
1                | test       | 1.00               | 1
2                | test 2    | 2.50               | 1

 

 

Here I would link the table with an ON condition in the Query like this:

SELECT products.*, categories.category_name FROM categories JOIN products ON categories.id=products.category_id WHERE categories.category='example'

 

 

 

EDIT:

You posted while I was working on mine.

 

Is category in useful links the same value as category_name in categories?

 

If so you can do this:

SELECT useful_links.*, categories.category_name FROM useful_links JOIN categories ON useful_links.category=categories.category_name WHERE (add your WHERE condition here.)

[oraya]

Ah in that case category_name and category matches.  I will have a go at it now and report back how it went.  Thank you so very much for your help, i've spent a couple hours googling for an answer and not getting very far.  There seems to be lots of info on excel tables but not on the topic I was looking for..

 

That's ok, I wasn't sure if you had left the forum.  Once again very grateful.

 

Oraya