[RESOLVED] code help with Join and select

Simplyput

Okay I know I am butchering this code, but I am a hack and trying to get better on my php skills, so I have stepped a little out of my comfort zone and created this mess below.

"What are you trying to do?" you may ask simple.

I am trying to join two tables in able to get the (band_id) from one table (silly_band_info) to be able to populate the (user_band_id) in table roto_user. Can some one take a look at my code and tell me where I went wrong. I am learning and will be greatly appreciative.

Thanks


$db = new sql_db("localhost", "xxxx", "xxxx", "mydb");                                

$db->sql_query "SELECT * FROM {silly_band_info} b  

LEFT JOIN {roto_user} u ON b.band_name = u.user_nickname
WHERE band_name is not null";
$db->sql_query("insert into roto_user(user_band_id) value (band_id) FROM silly_band_info WHERE band_id is not null");
if (!$db_selected) {
die ('Can\'t use mydb : ' . mysql_error());

Aybabtu

Is the table roto_user already populated with data and you need to update it? if so use the UPDATE command not INSERT. Let me know. I hate join statements so I tend to use the syntax "WHERE a.table1_field_to_match = b.table2_field_to_match"

If you are updating try selecting the data and then in a while loop updating the table you need. Something like the following:

<?


$connection = mysql_connect('server_name', 'user_name', 'user_pass');
$query "select * from table_name1 a, table_name2 b Where a.field_to_match = b.field_to_match";
$result = mysql_db_query('db_name',$query,$connection) or die (mysql_error());
while ($row = mysql_fetch_assoc($result))
		{
                 $query2 = "update table_name set db_field_name = '".$row['field_from_query']."' where db_field_name = '".$row['another_field_from_query']."'";
                 $result2 = mysql_db_query('db_name',$query2,$connection) or die (mysql_error());

?>

What this is supposed to do is after each row is selected from the query, grab the values and then update the table with the values. Keep in mind that this is for a mysql database and may not be the correct syntax for your DB.

I make no guarentee that this will work. Just something to get you thinking. I still need help from here, so in no way am I a master.

Aybabtu.

Simplyput

Hey your solution worked. I just tweeked it a little to fit my DB and such and pow it worked. Now I have an new problem so it requires a new thread.