Mysql insert not working

[jay7981]

Hey all,

 

i have a multi table instert i am trying to do and its not inserting and is only displaying blank could i get a second set of eyes to check the code and tell me where i went wrong? i am using MySQL 4.1.22

 

<?php
include './config.php'; //(holds the actual database info and some variables for tables (that im not actually using))
//include './access.php'; //(Holds some array variables that i use in other pages.)


$db3 = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db ($database);

	$authid = "'" . mysql_real_escape_string($_POST['authid']) . "'";
	$name = "'" . mysql_real_escape_string($_POST['name']) . "'"; 
	$email = "'" . mysql_real_escape_string($_POST['email']) . "'";
	$fid = "'" . mysql_real_escape_string($_POST['fid']) . "'";
	$rank = "'" . mysql_real_escape_string($_POST['rank']) . "'";
	$access = "'" . mysql_real_escape_string($_POST['access']) . "'";
	$admin_id = "'" . mysql_real_escape_string($_POST['admin_id']) . "'";
	$group_id = "'" . mysql_real_escape_string($_POST['group_id']) . "'";

$query = "INSERT INTO bioclan.clan_members a INNER JOIN bioclan.admins b ON a.authid = b.auth INNER JOIN bioclan.sm_admins c ON a.authid = c.identity INNER JOIN bioclan.sm_groups d ON d.id = e.group_id INNER JOIN bioclan.sm_admins_groups e ON c.id = e.admin_id (a.authid,a.name,a.email,a.fid,a.rank,b.auth,b.name,b.access,c.identity,c.name,e.admin_id,e.group_id) VALUES ($authid,$name,$email,$fid,$rank,$authid,$name,$access,$authid,$name,$admin_id,$group_id)";


if ($result = mysql_query($query)) 
{  
	 if (mysql_affected_rows() == 1) 
	{    
		echo "<font size=\"+1\" color=\"red\">Update OK!</font>"; 
		mysql_close($db3);
		include ('./includes/spry.php');
	} 
	else 
	{    
		echo "<font size=\"+1\" color=\"red\">Oops! Something went wrong</font>";
		mysql_close($db3);
		include ('./includes/spry.php');
	}
}
?>

[jay7981]

here are the table structures, i hope that i have provided enough information.

 

MySQL 4.1.22

 

clan_members table (a)

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| authid      | varchar(36)  |      | PRI |         |       |
| rank        | varchar(33)  | YES  |     | NULL    |       |
| name        | varchar(33)  | YES  |     | NULL    |       |
| email       | varchar(255) | YES  |     | NULL    |       |
| fid         | varchar(255) | YES  |     | NULL    |       |
| avatar      | varchar(255) | YES  |     | NULL    |       |
| private_pin | varchar(255) |      |     |         |       |
+-------------+--------------+------+-----+---------+-------+

 

admins table (b)

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| auth     | varchar(32) |      | PRI |         |       |
| name     | varchar(32) |      |     |         |       |
| password | varchar(32) |      |     |         |       |
| access   | varchar(32) |      |     |         |       |
| flags    | varchar(32) |      |     | ce      |       |
+----------+-------------+------+-----+---------+-------+

 

sm_admins table ©

+----------+---------------------------+------+-----+---------+----------------+
| Field    | Type                      | Null | Key | Default | Extra          |
+----------+---------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned          |      | PRI | NULL    | auto_increment |
| authtype | enum('steam','name','ip') |      |     | steam   |                |
| identity | varchar(65)               |      |     |         |                |
| password | varchar(65)               | YES  |     | NULL    |                |
| flags    | varchar(30)               |      |     |         |                |
| name     | varchar(65)               |      |     |         |                |
| immunity | int(10) unsigned          |      |     | 0       |                |
+----------+---------------------------+------+-----+---------+----------------+

 

sm_groups table (d)

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(10) unsigned |      | PRI | NULL    | auto_increment |
| flags          | varchar(30)      |      |     |         |                |
| name           | varchar(120)     |      |     |         |                |
| immunity_level | int(1) unsigned  |      |     | 0       |                |
+----------------+------------------+------+-----+---------+----------------+

 

sm_admins_groups table (e)

+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| admin_id      | int(10) unsigned |      | PRI | 0       |       |
| group_id      | int(10) unsigned |      | PRI | 0       |       |
| inherit_order | int(10)          |      |     | 0       |       |
+---------------+------------------+------+-----+---------+-------+

 

the query

<?php

$query = "INSERT INTO bioclan.clan_members a 
INNER JOIN bioclan.admins b ON a.authid = b.auth 
INNER JOIN bioclan.sm_admins c ON a.authid = c.identity 
INNER JOIN bioclan.sm_groups d ON d.id = e.group_id 
INNER JOIN bioclan.sm_admins_groups e ON c.id = e.admin_id 
(a.authid,a.name,a.email,a.fid,a.rank,b.auth,b.name,b.access,c.identity,c.name,e.admin_id,e.group_id) 
VALUES 
($authid,$name,$email,$fid,$rank,$authid,$name,$access,$authid,$name,$admin_id,$group_id)";
?>

[PFMaBiSmAd]

The syntax of your INSERT query is not valid. The only current way of getting mysql to perform a multi-table insert would be to set up a TRIGGER to insert data into the dependent tables - http://dev.mysql.com/doc/refman/5.1/en/faqs-general.html#qandaitem-23-1-1-3

[jay7981]

ok i changed my query to this

 

<?php
$query = "INSERT INTO bioclan.clan_members (authid,name,email,fid,rank) VALUES ($authid,$name,$email,$fid,$rank);";
$query .= "INSERT INTO bioclan.admins (auth,name,access) VALUES ($authid,$name,$access);";
$query .= "INSERT INTO bioclan.sm_admins (identity,name) VALUES ($authid,$name);";
?>

 

and it is still doing the same thing, so i echoed $query and copied it into MySQL Console and it worked, im not sure but i am positive there are more ways than just using triggers, atleast i hope so ... also i just read up on that link about triggers and i am already lost.. ..

 

the echo looks like this btw ...

INSERT INTO bioclan.clan_members (authid,name,email,fid,rank) VALUES ('STEAM_0:0:000000','BOB','bob@email.com','000000000000','9');INSERT INTO bioclan.admins (auth,name,access) VALUES ('STEAM_0:0:000000','BOB','abcdefghijklmnopqrstu');INSERT INTO bioclan.sm_admins (identity,name) VALUES ('STEAM_0:0:000000','BOB');INSERT INTO bioclan.sm_admins_groups (admin_id,group_id) VALUES ('65','6')

[PFMaBiSmAd]

You must execute each query individually, for a couple of reasons -

 

1) You would want to use error checking, error reporting, and error recovery logic on each query and

2) mysql_query() does not support multiple queries separated by semi-colons ; because too many 'programmers' are not validating and escaping their data to prevent sql injection.

 

Executing multiple queries manually against the database is a special case where a human is involved in the process and he can address any errors that occur.  A program attempting to do the same thing must be bullet proof so that an error in one step does not carry through and insert invalid data in the following steps.

[jay7981]

ok then can you help me build this query to where it will work correctly? i have read 4 different pages dealing with triggers and i am still clueless