Update, insert into statements don't work properly

[UD2006]

I have made a form in php (a contact database), I have 2 tables (because of 2 groups of contacts). When a person enters a contacts info in the form, an sql script checks if the contact is already in the other table, ifso the contact_id (random generated) from table 2 needs to be put into a "shared" table and the contact info inserted into the form needs to be put into table 1 (with the contact_id of table 2).

The sql statements (scripts) if have at the moment, checks if the contact inserted is indeed in the other table, but the inserting of the contact_id, isn't working.

Can someone take a closer look at the sql code I use and see if I made a error?

Thanks in advanced.

//set the random id length
$random_id_length = 6;

//generate a random id encrypt it and store it in $rnd_id
$rnd_id = crypt(uniqid(rand(),1));

//to remove any slashes that might have come
$rnd_id = strip_tags(stripslashes($rnd_id));

//Removing any . or / and reversing the string
$rnd_id = str_replace(".","",$rnd_id);
$rnd_id = strrev(str_replace("/","",$rnd_id));

//finally I take the first 10 characters from the $rnd_id
$rnd_id = substr($rnd_id,0,$random_id_length);


$sql="SELECT * FROM `vmb_contacts` WHERE `vmb_zip`='$nbs_vmb_zip' AND `vmb_number`='$nbs_vmb_number' AND `vmb_contact_name`='$nbs_vmb_contact_name'";
if (!($result = mysql_query($sql)))
{
}
$search = mysql_num_rows($result);

if (0 < $search)
{
$sql2 = "INSERT INTO `nbs_vmb_contacts_shared` (`shared_contact_id`) SELECT `vmb_contact_id` FROM vmb_contacts WHERE `vmb_zip`='$nbs_vmb_zip' AND `vmb_number`='$nbs_vmb_number' AND `vmb_contact_name`='$nbs_vmb_contact_name'";
$result2 = mysql_query($sql2);

$sql = "INSERT INTO `nbs_contacts` (
`nbs_group_id`,`nbs_contact_id`,`nbs_contact_aanhef`,`nbs_contact_prechar`,`nbs_contact_name`,`nbs_c ontact_tussenv`,`nbs_contact_sur`,`nbs_address`,`nbs_number`,`nbs_numberadd`,`nbs_postbusnr`,`nbs_zi p`,`nbs_city`,`nbs_country`,`nbs_phone`,`nbs_mobphone`,`nbs_emailadres`,`nbs_contact_day`,`nbs_conta ct_month`,`nbs_contact_year`,`nbs_lid_vmb`,`nbs_selfburns`,`nbs_year`,`nbs_relativeto`,`nbs_profrela tie`,`nbs_psychonet`,`nbs_revalinet`,`nbs_reintegranet`,`nbs_juridischehulp`,`nbs_nomail`,`nbs_comme nts`)
VALUES
('$nbs_vmb_group_id','','$nbs_vmb_contact_aanhef','$nbs_vmb_contact_prechar','$nbs_vmb_contact_name' ,'$nbs_vmb_contact_tussenv','$nbs_vmb_contact_sur','$nbs_vmb_address','$nbs_vmb_number','$nbs_vmb_nu mberadd','$nbs_vmb_postbusnr','$nbs_vmb_zip','$nbs_vmb_city','$nbs_vmb_country','$nbs_vmb_phone','$n bs_vmb_mobphone','$nbs_vmb_emailadres','$nbs_vmb_contact_day','$nbs_vmb_contact_month','$nbs_vmb_con tact_year','$nbs_vmb_lid_vmb','$nbs_vmb_selfburns','$nbs_vmb_year','$nbs_vmb_relativeto','$nbs_vmb_p rofrelatie','$nbs_vmb_psychonet','$nbs_vmb_revalinet','$nbs_vmb_reintegranet','$nbs_vmb_juridischehu lp','$nbs_vmb_nomail','$nbs_vmb_comments', (SELECT `vmb_contact_id` FROM vmb_contacts WHERE `vmb_zip`='$nbs_vmb_zip' AND `vmb_number`='$nbs_vmb_number' AND `vmb_contact_name`='$nbs_vmb_contact_name')
)";
$result = mysql_query($sql);
}
else
{
$sql = "INSERT INTO `nbs_contacts` (
`nbs_group_id`,`nbs_contact_id`,`nbs_contact_aanhef`,`nbs_contact_prechar`,`nbs_contact_name`,`nbs_c ontact_tussenv`,`nbs_contact_sur`,`nbs_address`,`nbs_number`,`nbs_numberadd`,`nbs_postbusnr`,`nbs_zi p`,`nbs_city`,`nbs_country`,`nbs_phone`,`nbs_mobphone`,`nbs_emailadres`,`nbs_contact_day`,`nbs_conta ct_month`,`nbs_contact_year`,`nbs_lid_vmb`,`nbs_selfburns`,`nbs_year`,`nbs_relativeto`,`nbs_profrela tie`,`nbs_psychonet`,`nbs_revalinet`,`nbs_reintegranet`,`nbs_juridischehulp`,`nbs_nomail`,`nbs_comme nts`)
VALUES
('$nbs_vmb_group_id','$rnd_id','$nbs_vmb_contact_aanhef','$nbs_vmb_contact_prechar','$nbs_vmb_contac t_name','$nbs_vmb_contact_tussenv','$nbs_vmb_contact_sur','$nbs_vmb_address','$nbs_vmb_number','$nbs _vmb_numberadd','$nbs_vmb_postbusnr','$nbs_vmb_zip','$nbs_vmb_city','$nbs_vmb_country','$nbs_vmb_pho ne','$nbs_vmb_mobphone','$nbs_vmb_emailadres','$nbs_vmb_contact_day','$nbs_vmb_contact_month','$nbs_ vmb_contact_year','$nbs_vmb_lid_vmb','$nbs_vmb_selfburns','$nbs_vmb_year','$nbs_vmb_relativeto','$nb s_vmb_profrelatie','$nbs_vmb_psychonet','$nbs_vmb_revalinet','$nbs_vmb_reintegranet','$nbs_vmb_jurid ischehulp','$nbs_vmb_nomail','$nbs_vmb_comments')";
$result = mysql_query($sql);

$sql = "UPDATE `nbs_contacts` SET `nbs_contact_id`= nbs_vmb_contacts_shared.shared_contact_id FROM nbs_vmb_contacts_shared WHERE `nbs_vmb_contacts_shared.shared_contact_id`='vmb_contacts.vmb_contact_id'";
// $result = mysql_query($sql)
// }

//$query="INSERT INTO nbs_vmb_events (nbs_vmb_contact_id, ScarWars_2009, Brandwondendag_2009, 25_plus_2009, ScarTrek_2009, Zwemmen_2009, LLL_2009, Terug_naar_School_2009, Terug_naar_Werk_2009, Verder_met_littekens_2009, P2daagse_2009)
//VALUES
//('$rnd_id','$_POST[ScarWars_2009]','$_POST[Brandwondendag_2009]','$_POST[25_plus_2009]','$_POST[ScarTrek_2009]','$_POST[Zwemmen_2009]','$_POST[LLL_2009]','$_POST[Terug_naar_School_2009]','$_POST[Terug_naar_Werk_2009]','$_POST[Verder_met_littekens_2009]','$_POST[P2daagse]')";
}

if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo('<SCRIPT>
alert("Nieuw contact is toegevoegd aan database."); location.href("index.php");</SCRIPT>');

mysql_close($con)

[abduraooft]

Can someone take a closer look at the sql code I use and see if I made a error? It'd be easy for you, if you add proper error checks, like
$result = mysql_query($sql) or die(mysql_error()); and test for the errors in your queries.

[UD2006]

It'd be easy for you, if you add proper error checks, like
$result = mysql_query($sql) or die(mysql_error()); and test for the errors in your queries.

Oh sorry I already did that I I found out that in the second insert into SQL statement I had an SELECT statement what caused an error.

But this select statement is needed to get the contact_id from table2 and insert it into contact_id of table1.

Do I need to perform an Update statement to get the contact_id from table2 to table1?

[clunk.werclick]

Is it *this* statement that is the problem?
$sql2 = "INSERT INTO `nbs_vmb_contacts_shared` (`shared_contact_id`) SELECT `vmb_contact_id` FROM vmb_contacts WHERE `vmb_zip`='$nbs_vmb_zip' AND `vmb_number`='$nbs_vmb_number' AND `vmb_contact_name`='$nbs_vmb_contact_name'";

A couple of things;

You've used `backticks` on all fields and tables apart from vmb_contacts after the FROM. Probably not an issue, but different from everything else you have in the statement.

If you drop the INSERT INTO and just run the select statement, are you getting any results for vmb_contact_id?

Aside; I don't see any input cleansing going on to protect the database from SQL Injection attacks.

[UD2006]

Is it *this* statement that is the problem?
$sql2 = "INSERT INTO `nbs_vmb_contacts_shared` (`shared_contact_id`) SELECT `vmb_contact_id` FROM vmb_contacts WHERE `vmb_zip`='$nbs_vmb_zip' AND `vmb_number`='$nbs_vmb_number' AND `vmb_contact_name`='$nbs_vmb_contact_name'";


Sorry it's the statement after this one. (the longer one), you see a select statement almost at the end.

I'll look into the security issue (its not a big deal, because it's being used in our office), but nevertheless it can be usefull.

[clunk.werclick]

1. Does this query complete on it's own?
SELECT `vmb_contact_id` FROM vmb_contacts WHERE `vmb_zip`='$nbs_vmb_zip' AND `vmb_number`='$nbs_vmb_number' AND `vmb_contact_name`='$nbs_vmb_contact_name'
{if not, you'll be missing a field}

2. If you ECHO $sql to the screen - does the query have the data in it you are expecting?

This may just be the way you've cut/pasted - but check these are not typos - extra spaces appear to have crept in:
1. Just before VALUEs do you have a typo? Is that space meant to be there???? `nbs_comme nts` (is this just a display issue here???)
2. Is this a typo: nbs_c ontact_tussenv
3. Is this a typo: nbs_zi p
4. Is this a typo: nbs_conta ct_month
5. Is this a typo: nbs_profrela tie
{note the extra spaces}

When comparing text fields I was brought up to use LIKE not =. I may well have been taught wrong but assuming variable $nbs_vmb_zip contained text, `vmb_zip`='$nbs_vmb_zip' for me would be `vmb_zip`LIKE '$nbs_vmb_zip'. It may be nothing, but I'm not sure if it does numerical evaluation with = ??? I'm too old to remember :-)

[Old Pedant]

No, using LIKE is a *mistake* unless you also use one or more wildcards (% characters).

If you have no wildcards, then LIKE gives results identical to = but can cost more in performance time.

Too old to remember???? Nobody is as old as I am. <snicker />

[clunk.werclick]

Thanks fella, I was trying to remember why I was using it! {bad habit} x many ;-)

[Old Pedant]

That UPDATE query makes no sense.

First of all, you can *NOT* use back ticks around tablename.fieldname as in
`nbs_vmb_contacts_shared.shared_contact_id`
You *MUST* apply the separately to the table and field names, thus:
`nbs_vmb_contacts_shared`.`shared_contact_id`

But there is NO REASON to use back ticks with those names. They can't possibly be MySQL keywords and they are legal variable names, so the back ticks are just a waste of time.

So, making the code more readable, we get to this:

$sql = "UPDATE nbs_contacts "
. " SET nbs_contact_id = nbs_vmb_contacts_shared.shared_contact_id "
. " FROM nbs_vmb_contacts_shared "
. " WHERE nbs_vmb_contacts_shared.shared_contact_id=vmb_contacts.vmb_contact_id ";


But that makes no sense, either. Your WHERE clause mentions a table named vmb_contacts when that table is not mentioned in the UPDATE or FROM clauses.

Finally, none of that is the right syntax in MySQL for a multiple-table UPDATE. (It's about right for some other DBs, but not for MySQL.)

This is *UTTERLY UNTESTED*, but I *think* what you are after is this:


$sql = "UPDATE nbs_contacts, nbs_vmb_contacts_shared, vmb_contacts "
. " SET nbs_contacts.nbs_contact_id = nbs_vmb_contacts_shared.shared_contact_id "
. " WHERE nbs_vmb_contacts_shared.shared_contact_id=vmb_contacts.vmb_contact_id ";

But no, looking at that further, you don't define WHICH record in nbs_contacts is to be updated. You are missing a JOIN condition. And I can't guess what it might need to be.