Question about connecting mysql database tables

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.
Schuyler KuhlAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
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
 
Schuyler KuhlAuthor Commented:
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
 
Schuyler KuhlAuthor Commented:
Dear Ray,

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

Thanks again,

Sky
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Ray PaseurCommented:
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
 
Schuyler KuhlAuthor Commented:
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
 
Ray PaseurCommented:
Thanks for the points - glad things are pointed in the right direction now! ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.