Help with MySQL

[yanks6rule]

I am trying to create a multi table database in MySQL and write to and retrieve information using MySQL. I am able to insert and call data easily enough going into one table, but I am having problems with connecting tables and using the idea of normalization.

I am starting simple with an online registration for alumni of an association. My first table in a database is called alumni and it stores there e-mail address, first and last name and year of graduation.

I want to link a second table called guests to this first table. When registering the form allows an alumnus to enter upto 4 guest with the relationship to that particular alumnus. I want to insert the guest names into a separate table that has a column with the id of the alumni they are associated with. This way I can call that database to get the names and relationship when making the nametags and information for the alumni meeting.

I am trying to use mulitple SQL statements with inserting foreign keys and indexes but I seem to be over my head slightly because when I insert I am able to put alumni in and guest in but the ID of the alumni does not insert into guest table to link them together.

Can someone help me with the general strategy of going about this using php to call a mysql database. I will provide what I have if will help.

Thanks
Yanks

[Rodney_H_]

I think I understand you. Sounds like a fun project.

Here is some logic that may help...

When alumnus registers:

1) Validate input / make sure info is complete
2) Process it / filter it / prepare it for mysql insertion
3) insert the data for the alumnus first
4) Use mysql_insert_id() function to determine the ID of the new alumnus record in your db
5) Enter the guest's information in their table with the ID of the alumnus to "connect" them

You may even create db table structure like this:

TABLES

1) Alumni
2) Guests
3) alumnus_to_guest
4) Events

Then the alumnus registers themselves and their guests once, but can re-use that information for multiple events. If you do that, you would use the mysql_insert_id() function to retrieve the alumnus id and the guests' ids and store them as variables. Then after they were all inserted and saved, store the IDs into the: alumnus_to_guest table...

Hope that helps.

[yanks6rule]

Rodney, thanks for the help. This does make a lot of sense and is a good way to do it, I did not know about insert_id function.

In the alumnus_to_guest table, will it just be two columns guestid and alumnus_id, to make it easy to call a list for RSVP? I am new to this connecting tables to one another and just want to make sure I have it running correctly.

Thanks again for the help

[Rodney_H_]

Yes, the alumnus_to_guest table could just have the two fields that links the alumnus ID to the guest ID.

I, however, out of habit mostly, give everything an id itself, so I would make it with three.

id
alumnus_id
guest_id

[yanks6rule]

Rodney,

You have been a great help and I think I got in a little over my head because I tried to write an over-zealous code. I had all the code working to put in the guest and the alumni data but not the data into the alumnus_to_guest table (I called this rsvp_list). Right now my code is below. I have a form setup to put the data in and I don't know enough about PHP to script in advanced features so I am put in four different guest fields so that you can add up to four guests and then tried writing if statements to try and say whether data was inputted into the guest lines. When I try to parse this code I get the error that says duplicate was made can you help me troubleshoot this code:

//Set variables for database and table

$table_alumni = "alumni";
$table_guest = "guest";
$table_rsvp = "rsvp_list";
//SQL to insert alumni info into database
mysql_select_db('chemdata') or exit(mysql_error());
$sql = "INSERT INTO $table_alumni SET lastname='$POST[lastname]', firstname='$POST[firstname]', mi='$POST[mi]', advisor='$POST[advisor]',
email='$POST[email]', year_grad='$POST[year]', degree='$POST[degree]', rsvp='$POST[attend]'";
$result = @($sql, $connection) or die(mysql_error());
$pid = mysql_insert_id();
mysql_close();
//if statements to determine if alumni entered a guest and insert guest into guest table as well as guest and alumni into rsvp table
if ($POST[glast] != "") {
include "mysql_connect_snacks.php";
mysql_select_db('chemdata') or exit(mysql_error());
$sql2 = "INSERT INTO $table_guest SET glastname='$POST[glast]', gfirstname='$POST[gfirst]', relation='$POST[relation]', pid='$pid'";
$result2 = @($sql2, $connection) or die(mysql_error());
$gid1 = mysql_insert_id();
$sql6 = "INSERT INTO $table_rsvp SET eid='1', pid='$pid', gid='$gid1'";
$result6 = @($sql6, $connection) or die(mysql_error());
mysql_close;
}

if ($POST[glast2] != "") {
include "mysql_connect_snacks.php";
mysql_select_db('chemdata') or exit(mysql_error());
$sql3 = "INSERT INTO $table_guest SET glastname='$POST[glast2]', gfirstname='$POST[gfirst2]', relation='$POST[relation2]', pid='$pid'";
$result3 = @($sql3, $connection) or die(mysql_error());
$gid2 = mysql_insert_id();
$sql7 = "INSERT INTO $table_rsvp SET eid='1', pid='$pid', gid='$gid2'";
$result8 = @($sql7, $connection) or die(mysql_error());
mysql_close;
}

if ($POST[glast3] != "") {
include "mysql_connect_snacks.php";
mysql_select_db('chemdata') or exit(mysql_error());
$sql4 = "INSERT INTO $table_guest SET glastname='$POST[glast3]', gfirstname='$POST[gfirst3]', relation='$POST[relation3]', pid='$pid'";
$result4 = @($sql4, $connection) or die(mysql_error());
$gid3 = mysql_insert_id();
$sql8 = "INSERT INTO $table_rsvp SET eid='1', pid='$pid', gid='$gid3'";
$result8 = @($sql8, $connection) or die(mysql_error());
mysql_close;
}

if ($POST[glast4] != "") {
include "mysql_connect_snacks.php";
mysql_select_db('chemdata') or exit(mysql_error());
$sql5 = "INSERT INTO $table_guest SET glastname='$POST[glast4]', gfirstname='$POST[gfirst4]', relation='$POST[relation4]', pid='$pid'";
$result5 = @($sql5, $connection) or die(mysql_error());
$gid4 = mysql_insert_id();
$sql9 = "INSERT INTO $table_rsvp SET eid='1', pid='$pid', gid='$gid4'";
$result9 = @($sql9, $connection) or die(mysql_error());
mysql_close;
}

[Rodney_H_]

Here is a simplification:

<?php
// array of required fields:
$required = array('lastname','firstname','mi','advisor','email','year','degree','attend');

// loop through required fields
foreach($required as $value)
{
	// if a value is not set or is empty:
	if(!isset($_POST[$value]) || trim($_POST[$value]) == '')
	{
		$errors[] = 'Your ' . $value . ' is still needed';
	}
}

// if there were any errors:
if(isset($errors))
{
	echo '<h2>Error!</h2>';
	echo '<p>The following errors occurred:</p>';
	echo '<ul>';
	foreach($errors as $value)
	{
		echo '<li>' . $value . '</li>';
	}
	echo '</ul>';
	echo '<p>Please go back and try again.</p>';
}

// else there were no errors:
else {

foreach($_POST as $key=>$value)
{
	if(trim($value) != '')
	{
		if(get_magic_quotes_gpc() == 1)
		{
			$$key = trim(stripslashes($value));
		}
		else {
			$$key = trim($value);
		}
	}
	else {
		$$key = '';
	}
}

// CONNECT TO DB:
$connection = mysql_connect('localhost', 'USER', '*****') or die(mysql_error());

// select the DB
$db = mysql_select_db('chemdata') or die(mysql_error());

// create a query statement:
$query = "INSERT INTO alumni
		 SET lastname = '" . mysql_real_escape_string($lastname) . "',
		 firstname = '" . mysql_real_escape_string($firstname) . "',
		 mi = '" . mysql_real_escape_string($mi) . "',
		 advisor = '" . mysql_real_escape_string($advisor) . "',
		 email = '" . mysql_real_escape_string($email) . "',
		 year_grad = '" . mysql_real_escape_string($year) . "',
		 degree = '" . mysql_real_escape_string($degree) . "',
		 rsvp = '" . mysql_real_escape_string($attend) . "'";

// execute the query
$result = mysql_query($query) or die(mysql_error());

// get the alumnus ID
$alumnusID = mysql_insert_id();

// if there is a guest:
if ($glast != '')
{
	$query = "INSERT INTO guest
			  SET glastname = '" . mysql_real_escape_string($glast) . "',
			  gfirstname = '" . mysql_real_escape_string($gfirst) . "',
			  relation = '" . mysql_real_escape_string($relation) . "' ";

	// execute the query
	$result = mysql_query($query) or die(mysql_error());

	// get the alumnus ID
	$guestID = mysql_insert_id();

	// NOW, relate the alumnus TO the guest:
	$query = "INSERT INTO rsvp_list
			  SET pid = " . $alumnusID  . ",
			  gid = " . $guestID;

	// execute the query
	$result = mysql_query($query) or die(mysql_error());
}

// close mysql
$close = mysql_close;

// confirm to alumnus/registrant:
echo 'you are good to go...';
}
?>

I think you will understand the logic here. Then, just carry it through for the other guests....

Hope this helps.

[yanks6rule]

Rodney,

I am going to give this a go today over the next couple hours to do two things, first is teach myself how this all works and then make it work with my case. I have heard elsewhere that I am subject to SQL injections and that I need to use mysql_real_escape_string() to properly escape quotes and other specials characters. What is this exactly doing I read the documentation on it but that is slightly over my head.

Lastly, reading through this I get the logic of everything except this section. Sorry I don't mean to have you be my own little instructor I have just learned more today than I have in the months teaching myself by reading book after book and article after article.

else {

foreach($_POST as $key=>$value)
{
    if(trim($value) != '')
    {
        if(get_magic_quotes_gpc() == 1)
        {
            $$key = trim(stripslashes($value));
        }
        else {
            $$key = trim($value);
        }
    }
    else {
        $$key = ''; 

Thank you

[Rodney_H_]

yanks6rule wrote:

I have heard elsewhere that I am subject to SQL injections and that I need to use mysql_real_escape_string() to properly escape quotes and other specials characters. What is this exactly doing I read the documentation on it but that is slightly over my head.

If you look at the code that I supplied, you will see that I use the mysql escape function to help PREVENT sql injection.

What does the function do? Basically, it escapes special characters. (Click the link for MORE info...)

yanks6rule wrote:

Lastly, reading through this I get the logic of everything except this section...

else {

foreach($_POST as $key=>$value)
{
    if(trim($value) != '')
    {
        if(get_magic_quotes_gpc() == 1)
        {
            $$key = trim(stripslashes($value));
        }
        else {
            $$key = trim($value);
        }
    }
    else {
        $$key = ''; 

The FOREACH loop goes through every single posted value from the form. Form elements may be set wihtout actually having a value, or having an empty value, so it checks to make sure there is a value that is not an empty string:

if(trim($value) != '')

IF there is a value, the next conditional checks to see if magic quotes are on:

if(get_magic_quotes_gpc() == 1)

If they are on, they could adversely effect the data because it would be semi-redundant with the mysql_real_escape_string() function. SO, if it IS on, we stript the slashes here:

$$key = trim(stripslashes($value));

If there is no value, it assigns an empty string to the variable:

$$key = '';

Hope that helps........

[yanks6rule]

Rodney,

You have been a great help, I was able to work everything out and it works great now. You have been very patient and I appreciate it. I learned quite a bit, I still have to digest why some of the variables have double $$, but I will get there.

Thanks again