Selecting then Inserting

[verdrm]

I have an interesting issue...it may not be that hard to do...but...I would like to select data from a column while inserting data into the database at the same time...but the data from the SELECT statement must be inserted at the same time into the row being added.

 

Is this possible? If so how?

[marcus]

$sql = "INSERT INTO `table` (`name`,`flavor`) VALUES('marcus','chocolatey')";
$res = mysql_query($sql) or die(mysql_error());
$id = mysql_insert_id();
$sql2 = "SELECT * FROM `table` WHERE `id`='".$id."'";
$res2 = mysql_query($sql2) or die(mysql_error());
$row = mysql_fetch_assoc($res2);
echo "Your name is " . $row['name'] . " and your favorite flavor is " . $row['flavor'];

[verdrm]

That isn't what I want to do, but thanks for posting.

 

What I want to do is SELECT data and insert it with other data coming from a form.

 

Example:

 

$selected = mysql_query("SELECT text FROM worksheets WHERE id = '".$_SESSION['id']."' AND something = '$title'");

 

$sql =mysql_query("INSERT INTO table (stuff...) VALUES (stuff from form PLUS selected stuff above);

 

This does not work, however, which I know. Any ideas?

[marcus]

Oh i see.

 

$sql = "SELECT text FORM `worksheets` WHERE `id`='".$_SESSION['id']."' AND `something`='".$title."'";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res); // you only selected text, so you can only return text

$sql2 = "INSERT INTO `table` (`text`) VALUES('".$row['text']."')";
$res2 = mysql_query($sql2) or die(mysql_error());

[verdrm]

That's similar to what I did, but that does not work either. Any suggestions?

[trq]

That's similar to what I did, but that does not work either.

 

Post your code.

[phpSensei]

try echo'ing the row $text, and see if it has any values.

[verdrm]

The code I used is almost identical to that poster's above, and when I echoed the row it showed my data.

[tippy_102]

almost

That might be your problem.

[jeffjohnvol]

I may be misunderstanding what you are trying to do, but did you want to populate the insert from a select? If so, somthing like the following could work (much alternations needed, this is from some of my old code):

 

$sqltext="insert into nuke_role_perm_xref (select NULL, '$role_id','M',mid from nuke_modules where mid not in (select xid from nuke_role_perm_xref where role_id='$role_id' and id_type='M'))";

 

Edit: to be more readable.....

 

insert into table1 (field1, field2, field3) (select b.field1, b.field2, b.field3 from table2 b where ......)

where ......

 

If you need to get fancier by correlating more tables, or join to the table you are inserting to, let me know and I can go into more detail if I knew your table layout.

[verdrm]

That's sort of what I want to do...here is my code:

 

$query = mysql_query("SELECT text FROM table WHERE something = '$something' ");

 

while($row = mysql_fetch_assoc($query))

 

$insert = mysql_query("INSERT INTO table (1,2,3,4) VALUES ('$id','$something','$something', '{$row['text']}') ");

 

 

I can echo the row but it will not insert. Funny thing is that this same script works on another page...I just can't figure out what the difference is....

[priti]

That's sort of what I want to do...here is my code:

 

$query = mysql_query("SELECT text FROM table WHERE something = '$something' ");

 

while($row = mysql_fetch_assoc($query))

 

$insert = mysql_query("INSERT INTO table (1,2,3,4) VALUES ('$id','$something','$something', '{$row['text']}') ");

 

 

I can echo the row but it will not insert. Funny thing is that this same script works on another page...I just can't figure out what the difference is....

Here i See...response in CAPS

 

$query = mysql_query("SELECT text FROM table WHERE something = '$something' ");

--YOU FETCHED TEXT

 

while($row = mysql_fetch_assoc($query))

--RUNNING A LOOP TO INSERT THE FETCHED TEXT

 

$insert = mysql_query("INSERT INTO table (1,2,3,4) VALUES ('$id','$something','$something', '{$row['text']}') ");

 

--here you are inserting the fetched and form posted data.If your register_global in php.ini is off then you have to fetch  posted form data values from $_POST array.you should not use {} in query i never use it in plain queries.Even till now you are not able to get the idea then copy the query and run in SQL mode in mysql and see what output you are receiving.

 

the logic seems to be alright but because you are not posting the correct piece of code it would be diffcult for any one to help you.

 

anyways try these hope it solves your problem .

[jeffjohnvol]

 

 

I can echo the row but it will not insert. Funny thing is that this same script works on another page...I just can't figure out what the difference is....

 

You should be able to do it with one SQL statement, no need to have a loop.  In many cases, the trick is that you have to be wary of duplicates.

 

Also, if you have an autonumber field, you need to make that value '' or NULL.

 

here's another prototype that watches for duplicates

 

"insert into table1 (f1,f2,f3,f4)

(select f1,f2,f3,f4 from table2

where table.f1=table2.f1)  // or whatever join criteria you like.

 

If you send your table structure I could help more.

[verdrm]

Thanks for replying, guys! Can someone just provided a clear example, though, of what I would do using the row name 'text'?

 

If this is what I should do:

 

"insert into table1 (f1,f2,f3,f4)

(select f1,f2,f3,f4 from table2

where table.f1=table2.f1)

 

...where do I put the result of the select statement in 'values'? So VALUES('','','')...where does the result of that select statement go? How do I express it using the correct syntax?

[jeffjohnvol]

Okay, its pretty simple.  You just replace

 

values("1","2","3"....)

 

with

(select field1, field2, field3 from table2).

 

The field list of your "insert into table1 (field1,field2,field3)" needs to be lined up with your select.

 

You can also replace some of the fields in your select with constants.  For example:

 

select field1, "Boat", field2, 23.43 from table2

 

if you send the table structure with the criteria, I can write it up for you.  Its what I do all day at the office.

[verdrm]

What I am still confused about is where the select statement goes.....

 

$ins = mysql_query("INSERT INTO table (a,b,C,d) VALUES ('a','b','C','d')");

 

How do I take the above (with 'C' in bold being the row I need from another table) and make it work?

 

[jeffjohnvol]

Sorry, I don't think my last post made it.

 

If your 3 constant values where 'Jeff', 'Robbie" and 'Jimmy' and your field was last_name, you would put may have something like this before:

 

$last = $row['last_name'];

values('Jeff', 'Robbie', '$last', 'Jimmy')

 

Replace that with

 

(SELECT 'Jeff', 'Robbie', last_name, 'Jimmy' from table2 where......)

 

Its that simple.

[redarrow]

select lession i think goes here<<<<<<<<<<<<<<<<<

<?php //use ?php format as it defualt in php.ini and safe opening for php programming.

//database connection

//database correct connection format, even theo there other ways......

$db=mysql_connect("localhost or ip or url","name of user","password for user");
$db_result=mysql_select_db("database using",$db);

// selecting a standard database query of all entrys.

$sql1="SELECT * FROM whatever";
$sql_result1=mysql($sql1);
while(list($key,$val)=mysql_fetch_assoc($sql_result1))){
echo "$key <br> $val";
}
//The above will list all entry's via $key $val useing the while loop and list function....

// selecting a name via it were clause and other info as needed....

$sql2="SELECT * FROM whatever where name='petter'";
$sql_result2=mysql($sql2);
while($row=mysql_fetch_assoc($sql_result2)){
echo " ".$row['name']."  <br> ".$row['id']." ";
}

// The above will echo out the name off petter and only petteres info as stated in the were //clause and any other record needed via the while loop of petter only and it field name $row[''] //asked, example the $row id was added of petters only.....


// selecting only id and name via where name='petter'

$sql3="SELECT name,id FROM whatever where name='petter'";
$sql3_result=mysql($sql3);
while($row=mysql_fetch_assoc($sql_result3))){
echo " ".$row['name']."  <br> ".$row['id']." ";
}

// same as last above example, except we can only see the id and name as it been seleted via //the select query and it in relation with the name petter only set via the were clause........


// selecting only name via where name='petter and using no while loop ...

$sql4="SELECT name FROM whatever where name='petter'";
$sql4_result=mysql($sql4);
$row=mysql_fetch_assoc($sql_result4));
echo " ".$row['name']." ";


// same as last above example, except we can only see the id and name as it been seleted via //the select query and it in relation with the name petter only set via the were clause........
// but also were only getting one result of a name so we dont need a while loop, but if we //wanted to also see the id of petter we should use the while loop........


//WARNING old programming before sessions.

//this is bad programming but it still happends belevle it or not, let say we wanted to select //petter info via his id but we have no session set and dont no petters id.......


// first we get petters id via one select statement and no while loop........

sql5="SELECT id FROM whatever where name='petter'";
$sql5_result=mysql($sql5);
$row1=mysql_fetch_assoc($sql_result5));

//Another select statement to get petters info via the above id select statement...

sql6="SELECT * FROM whatever where name='petter' and user_id=' ".$row1['id']." ' ";
$sql6_result=mysql($sql6);
while($row2=mysql_fetch_assoc($sql_result6));
echo"  ".$row2['user_id']." <br> ".$row2['name']." <br> ".$row2['age']." ";
}

// the above selects the first select statement to get the users id then the secound echo the info............

// what ever way you select users info all you need to do is use any other mysql function like //DELETE,UPDATE in the while loop to do anythink elese you need to do........
//if you got the users id in a session just use that session in a where clause..

my littele tutoral good luck 
?>

[verdrm]

Select lesson does not go here. You haven't read this thread. I have no problem with basic select statements.