msql query

[daniel0816]

How do I change the query below so that MANU_ID which comes from the Manufacturers table is inserted into the Models table.

 

$sql="INSERT INTO Models(MOD_Name)
VALUES
 ('$_POST[model]')";

 

Any help would be greatly appreciated thanks

[Muddy_Funster]

this isn't a PHP question...

your looking for somthing along the lines of

INSERT INTO table1 (value1) SELECT value1 FROM table2 WHERE value2 = 'yourValueHere'

[daniel0816]

Any other suggestions would be greatly appreciated. Thanks

[Muddy_Funster]

and the problem with my suggestion is?

[daniel0816]

TBH it doesn't make sense to me this is the query:

 

$sql="INSERT INTO Models(MANU_ID, MOD_Name) SELECT MANU_ID FROM Manufacturers WHERE MOD_Name = '$_POST[model]'";

 

and I don't get the purpose of the function u posted because its not being called apologies for this am new to MySQL/php.

[daniel0816]

Has anyone got any suggestions anything ive tried isn't the correct syntax.

[DavidAM]

How do you know which MANU_ID you want associated with this "model"?

 

There really is not enough information in your question to provide the answer you seek. Is the Manufacturer specified on the form along with the model? Are you holding the ID in a session?

 

$manuID = [Whatever the value should be]
$model = $_POST['model']; # THIS NEEDS TO BE ESCAPED FOR YOUR DATABASE ENGINE

$sql = "INSERT INTO Models (MANU_ID, MOD_Name) VALUES( $manuID, '$model')";

Note: You need to validate and escape all input from the user to protect against SQL Injection as well as special characters in the value.

[daniel0816]

The MANU_ID is set to AUTO INCREMENT in the manufacturers table and so is the MOD_ID in the Models table. They both start at zero so each time a manufacturer or model gets submitted to the database the two ID's increment by one. Therefore the two ID'S will be the same. So if a model has the same ID as a manufacturer then that model is associated with that manufacturer.

Edited August 22, 2013 by daniel0816

[Muddy_Funster]

the function is in my signature - it's not directly related to your question.  The SQL you have won't work because you are trying to insert into two fields while only selecting one value to insert :

 

$sql="INSERT INTO Models(MANU_ID, MOD_Name) SELECT MANU_ID FROM Manufacturers WHERE MOD_Name = '$_POST[model]'";

MANU_ID and MOD_Name are your two fields and you are only selecting the value of MANU_ID from the Manufacturers table to put in there. You need to balance both sides for the query to work.

[Muddy_Funster]

The MANU_ID is set to AUTO INCREMENT in the manufacturers table and so is the MOD_ID. They both start at zero so each time a manufacturer or model gets submitted to the database the two ID's increment by one. Therefore the two ID'S will be the same. So if a model has the same ID as a manufacturer then the model is associated with that ID.

Sorry.....what?

 

Could you please explain in full the schema that you are using here?  Youre last statement sounds more than just a little bit like the wrong way to do things.

[daniel0816]

Sorry what u mean?

[Muddy_Funster]

break it all down for us.  What tables do you have, what information do they hold and how are they related to each other.

[daniel0816]

For this issue the only two tables concerned are the Manufacturers and Models table. These are the two queries I used to create the tables. The two tables are related through the foreign key identified in the models table. As I said earlier the primary keys in each table are set to auto increment. The MANU_Name and MOD_Name values are submitted through a form and each time these values gets submitted the primary keys increment.  

 

$sql=" CREATE TABLE Manufacturers(
 MANU_ID int AUTO_INCREMENT NOT NULL,
 MANU_Name varchar(40),
 PRIMARY KEY(MANU_ID)
 )";

 
 $sql=" CREATE TABLE Models(
 MOD_ID int AUTO_INCREMENT NOT NULL,
 MANU_ID int,
 MOD_Name varchar(40),
 PRIMARY KEY(MOD_ID),
 FOREIGN KEY(MANU_ID) REFERENCES Manufacturers(MANU_ID)
 )";
 

The query below inserts the model name into the models table. What I want to do is alter the query so that it takes the MANU_ID from the manufacturers table and inserts it into the models table but it doesn't take it out of the manufacturers table completely it copies it if u like, this is to indicate the relationship between the two tables. At the moment the MANU_ID doesn't show up in the Models table because I haven't said to put it there an that's what I am trying to do.

 

$sql="INSERT INTO Models(MOD_Name)
VALUES
 ('$_POST[model]')";

Edited August 22, 2013 by daniel0816

[kicken]

All you have to do is change your INSERT to:

$sql="INSERT INTO Models(MANU_ID, MOD_Name)
VALUES
 ($ManuId, '$_POST[model]')";

The question is then where do you get the value for $ManuId from? If in your code you've just done an INSERT into the Manufacturers table then you can use the last insert id function (check the manual for whatever api you are using) to retrieve the newly generated MANU_ID from the Manufacturers table. If you're trying to add a model for some already existing entry then you'd have to pass the MANU_ID via the $_GET or $_POST data.

[daniel0816]

Sorry but for this issue how do i go about using the last insert id function because I know what your on about and it would work am just unsure as to how to structure the code.

 

thanks

[daniel0816]

Do I need to pass any parameters below, there are no errors as such but it still isn't displaying the value for J_RefNum from the job table. 

$jRefNum = mysql_insert_id(parameters here????); 
$sql="INSERT INTO dataRecSpec(J_RefNum, DRS_Name)
VALUES
 ($jRefNum, $txt)";
 

[Muddy_Funster]

mysql has it's own internal command that gets the last insert id, try this:

 

$sql = "INSERT INTO dataRecSpec(J_RefNum, DRS_Name) VALUES(LAST_INSERT_ID(), '$txt')";

[daniel0816]

Thanks but I get the following error:

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

[Muddy_Funster]

hang on. do you have a last insert id to get? has there already been an insert performed on the same connection before this is being called?

[daniel0816]

Yea there should be

[Muddy_Funster]

should be? O_o

 

do a describe of your tables and show me what you are trying to do.  I think a triger may be a better way to go, but it depends on a couple of things.

[daniel0816]

OK I have a job table with a primary key J_RefNum. I also have a data recovery spec table with a primary key DRS_ID and a foreign key J_RefNum from the job table which indicates the relationship between the two tables. What I am trying to do is that when data gets submitted into the database the J_RefNum auto increments each time in the job table and that works ok but the J_RefNum doesn't show in the data recovery spec table. What I want to do is when data is submitted to the database and the J_RefNum increments in the job table it also inserts that number into the data recovery spec table. This is to indicate what data recovery spec is associated with which job.

[Muddy_Funster]

from your description, it would normaly be done the other way arround.  It would be standard to insert the appropriate DRS_ID refference into the job table as the DRS_ID is specific to the job, not the other way round.

 

still, what can be done is you can add a trigger to the job table that will run after every record insert and will insert values into the drs table.  It's effectivly a stored procedure that you don't even have to call once it's in place.

 

can you run the describe command on the tables (using phpMyAdmin or whichever interface you use) and post up the output? Just so I can get a better feel for what you are doing V's what I would reccomend.

[daniel0816]

Well all I am doing is trying to insert the value of the auto incremented J_RefNum into the data recovery spec table. 

AND in the data recovery spec table the J_RefNum is identified as the foreign key. It is too indicate the relationship between the two tables.

Below is the code that I am trying to use in order to do this:

 

$jRefNum = mysql_insert_id(); 
$sql="INSERT INTO dataRecSpec(J_RefNum, DRS_Name)
VALUES
 ($jRefNum, $txt)";

 

Thanks

[Muddy_Funster]

I get that you think the relationship is a complex one, that's why I asked you to run a describe on them and let us see what's there.  If it's proving to be this convoluted to perform a simple insert then it's most likely that the design needs some work.

 

one possible way could be to get the max id, but that is well south of being a reliable solution.

 

INSERT INTO dataRecSpec (J_RefNum, DRS_Name) VALUES(SELECT MAX(job_ID) FROM jobTableName, '$text')

 

But I really recomend against using this.  I don't get why mysql_insert_id() is returning 0 if you are sure the last query was the insert into the job table - it's only suppsed to return 0 if there was no auto_inc id generated by the last query.

 

As I say - I think this has become such a headache becase of a design flaw which we could possibly work to fix.