Solved

Question about connecting mysql database tables

Posted on 2011-09-15
6
124 Views
Last Modified: 2012-05-12
greetings.

If I have several tables such as "owner", "property", and "calendar" and lets say the owner table has a field called "record_index" which is an auto-increment type.  is it appropriate to use that field to connect a unique owner record to several property records?

Secondly, is there a way when using INSERT INTO owner_table to know what the value of that incremental field after the new row is inserted, based on $result = mysql_query($sql) or do I have to do a search for that record and then use the incremental field value from that search in my insert_into for the "property" table.

That is can I do three insert_into commands in a row and use the incremental field value from the prior insert in the second and third step?

Thanks very much for your help.
0
Comment
Question by:skykuhl
  • 3
  • 3
6 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 36544344
You will love this book:
http://www.sitepoint.com/books/phpmysql4/

Yes, it is appropriate to use a key from one table to logically connect the contents of a row with row(s) in other tables.

See the code snippet for some mysql examples, including a way to get the mysql_insert_id() after an INSERT.  Look near the end.
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
 

Author Comment

by:skykuhl
ID: 36544429
Dear Ray,

Thank you very much.  You have helped me before and I greatly appreciate your knowledge.

To clarify, If I am starting with this before my insert:

$db1 = mysql_connect("localhost",$db_user,$db_pass);
  mysql_select_db($DB,$db1);

then just after the insert I would have:

$id  = mysql_insert_id($db1);

Then I can use $id in my next insert.  Is this correct?

That doesn't reference the table so i assume that it would just the last table effected.

Also I assume this means that there is a limitation to one auto increment field per table.

Thanks again for your help.

Sky


0
 

Author Comment

by:skykuhl
ID: 36544478
Dear Ray,

I just tested it and what I wrote above is correct and it looks good.

Thanks again,

Sky
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36544512
I think you're right about one auto_increment column per table, and it looks like your understanding is correct.  Regarding this, "That doesn't reference the table so i assume that it would just the last table effected."  Be careful of any assumptions about that.  Check the man page for the notes here:
http://us3.php.net/manual/en/function.mysql-insert-id.php
0
 

Author Comment

by:skykuhl
ID: 36544531
Thank you.  My testing shows that it does work that way.  I just tried it and showed the $id value and it matched with the auto increment field in each of the tables. \

This is very excellent.  Thanks again for your help.

Best regards,

Sky
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36545575
Thanks for the points - glad things are pointed in the right direction now! ~Ray
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

EE introduced a new rating method known as Level, which displays in your avatar as LVL. The new Level is a numeric ranking that is based on your Points. This article discusses the rationale behind the new method and provides the mathematical formula…
With the shift in today’s hiring climate (http://blog.experts-exchange.com/ee-blog/5-tips-on-succeeding-in-the-new-gig-economy/?cid=Blog_031816), many companies are choosing to hire freelancers to get projects completed efficiently and inexpensively…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Notifications on Experts Exchange help you keep track of your activity and updates in one place. Watch this video to learn how to use them on the site to quickly access the content that matters to you.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now