Jump to content

placing two mysql tables in a query


oraya

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

<?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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.