[SOLVED] Please Help A Newbie... Simple Question With Mysql Where

[Looktrne]

Hello Im Paul

 

I Am Trying To Update A Database With Other Parts Of The Database

 

$mi="members id from another table"

 

Could you tell me why this querry is not executing??? is it a syntax problem?

 

I need to select the state field from dt_profile where the member id equals the id in $mi

 

$result = mysql_query("SELECT * FROM dt_profile

WHERE member_id='$mi'")

 

Any help would be greatly appreciated...

 

thanks

 

Paul

[grlayouts]

all depends on the full code? where your connecting to db right.

 

can you post the full script?

[Gmunky]

if $mi is a number, then you won't need the single quotes around the $mi in the query

[Barand]

The syntax looks OK. But if you only want the state field, specify that and not "*" or you just slow things down.

$result = mysql_query("SELECT state FROM dt_profile
                               WHERE member_id='$mi'");

 

And, if you want to know why a query isn't working, let mysql tell you.

$result = mysql_query("SELECT * FROM dt_profile
                              WHERE member_id='$mi'")
                              or die (mysql_error());

[Looktrne]

ok I tried this

 

but when I echo result

 

instead of showing a state it just says resource ID#

 

and keeps counting

 

how do I actually turn the result into the state so I can update the state in another table??

 

thanks for helping

 

ok lemme simplify this a little

 

the table dt_matchfinder had a state field

 

I need to take the state field from dt_profile table and update the dt_matchfinder with this state

 

making sure the member_id matches

 

the problem is the dt_matchfinder table has null set for many of the state field but in the dt_profile each member ID has a specific state and I need the same to be true in the dt_matchfinder table

 

A code example would be greatly appreciated

 

Have a great holiday weekend

 

Paul

[mmarif4u]

Can u show the full code.

Other how u pullout the state from dt_profile table with select.

 

[Looktrne]

the code does not exist yet here is something I wrote

 

$sql="SELECT * FROM dt_matchfinder;";

$result=mysql_query($sql);

 

 

while($row=mysql_fetch_assoc($result)){

$ms=$row['state'];

echo "Id= state is:".$ms;

 

this will pull all the states set in the dt_matchfinder only problem is only like 10 % are set

 

I need to pull state from dt_profile with the member_id from dt_profile

 

and place that state into the state of dt_matchfinder with same member_id

 

Sorry If seems confusing but thats as simple as I can put it they both have state fields and both have member_id fields I need the states from dt_profile to update the states in dt_matchfinder where the member_id is the same

 

thanks again for helping

 

total newbie

 

Paul

[mmarif4u]

A little bit confusion ok :

 

U want to pullout state from dt_profile table then why u use this:

$sql="SELECT * FROM dt_matchfinder;";

 

Do it like this:

$sql="SELECT state FROM dt_profile where member_id='$mid';";
$result=mysql_query($sql);


while($row=mysql_fetch_assoc($result)){
$ms=$row['state'];
echo "Id= state is:".$ms;

 

Than update ur dt_matchfinder table with this state like:

$sql="Update dt_matchfinder set state='$ms' where memberid='$mid';";
$result=mysql_query($sql);

[Looktrne]

oh sounds like this is what I will try to use

 

but still not sure if this is the easiest way

 

I need a loop

 

to scan all the member_id in the dt_profile and grab there states

 

then to write all those states into dt_matchfinder according to member id...

 

is there more code I need or is what you gave me going to do all this?

 

sorry to be such a pain but I am really new and I dont even understand alot of the therory behind some of the lines especially how the loops is going to work

 

Paul

[mmarif4u]

The code i mention is simple and work for u well.

The idea behind ur logic is accomplished in the code which i write in the prev post.

 

[Looktrne]

Ok

 

I will user that when I get a chance I really need sleep

 

thanks for your help

 

I will try it in a day or two when I get more free time

 

Paul

[Looktrne]

This is what I am trying to do

 

but when I add the update lines in the script does not run if I run without the second $result it displays the member id and state

 

these are the two variables I need to update the other table dt_matchfinder.... to update the state where the member_id= the one in dt_profile

 

am I trying to run multiply querrys in a invalid way???? I dunno

 

help please I know im getting close

 

 

Paul

[Barand]

As I said in your double-post asking this same question, You don't need the state repeated in both tables, but

 

UPDATE dt_matchfinder m 
INNER JOIN dt_profile p
       ON m.member_id = p.member_id
SET m.state = p.state

[Looktrne]

wow! that was too easy

 

I was thinking I had to run a while loop

 

when mysql could do all that so easy

 

I really need to read a book

 

thanks alot... but im not sure I learned much you made it so easy... anyway one quick question

 

do the index keys need to be updated after making that change??? because all the tables where indexed at some point in the past.. but it seems I only had to run that optimization script once and the database has been cruising right along.....

 

anyway thanks again

 

and do let me know on the index key thing

 

Paul

[Barand]

Indexes will update themselves.

[Looktrne]

ok thats what I thought

 

because they seemed no maitenance to them...

 

I have more questions for a new thread....

 

 

Paul