Solved

Question about connecting mysql database tables

Posted on 2011-09-15
6
127 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:Schuyler Kuhl
  • 3
  • 3
6 Comments
 
LVL 109

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:Schuyler Kuhl
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:Schuyler Kuhl
ID: 36544478
Dear Ray,

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

Thanks again,

Sky
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

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:Schuyler Kuhl
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 109

Expert Comment

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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This story has been written with permission from the scammed victim, a valued client of mine – identity protected by request.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
Articles on a wide range of technology and professional topics are available on Experts Exchange. These resources are written by members, for members, and can be written about any topic you feel passionate about. Learn how to best write an article t…

829 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