need help please putting together a mysql query in php

[gibsongk55]

Hi,

 

I am still learning php and I've been racking my brain to get some field data

from the mysql database and put the results into variables to use.

 

I need to do a query to get the data from two fields and place them in a variable.

 

I have this field from a form: $_REQUEST['linkurl']

 

So i need to check this form field against (tablename, fieldname) alldomain.domain_name

 

when the match is found I need to store the value of tablename, fieldname) domain.manual_approve into $x_manual_approve

 

and then check that rows userid field: (tablename, fieldname) alldomain.userid and equals (tablename, fieldname) register.id

 

the userid and id are matching from two different tables (tables alldomain and register).

 

When I get that match I need to get (tablename, fieldname) register.username and store the value into $x_username

 

Thanks much for any help,

 

Gibs

[wildteen88]

As you're needing to query multiple tables to get the necessary values you'll want to use a JOIN in order query these tables at the same time.

 

Reading your post I have came up with this untested query.

SELECT domain.manual_approve
       register.username
FROM alldomain
LEFT JOIN domain
    ON (domain.id = alldomain.id)
LEFT JOIN register
    ON(alldomain.userid = register.id)
WHERE alldomain.domain = '$link_url'

 

I don't know how you're linking the records in the domain table with the records within the alldomain table. I have guessed you're using an id field

LEFT JOIN domain
    ON (domain.id = alldomain.id)

[gibsongk55]

Hi,

 

Thanks for the reply.  Quick question though.  At the end of this query to get the values from the two fields, can I just assign after that?

 

Like this:

 

SELECT domain.manual_approve
       register.username
FROM alldomain
LEFT JOIN domain
    ON (domain.id = alldomain.id)
LEFT JOIN register
    ON(alldomain.userid = register.id)
WHERE alldomain.domain = '$link_url'
$x_manual_approve = domain.manual_approve
$x_username = register.username

 

Is that correct?

 

Thanks again,

 

Gibs

[gibsongk55]

Hi Again,

 

I looked over some more and there are some mistakes so I fixed them but haven't tested it yet until i can clarify how to get the field data.

 

The code updated is:

 

SELECT domain.manual_approve
       register.username
FROM alldomain
LEFT JOIN domain
    ON (domain.id = alldomain.id)
LEFT JOIN register
    ON(alldomain.userid = register.id)
WHERE alldomain.domain = '$link_url'
$x_manual_approve = domain.manual_approve
$x_username = register.username

 

 

Further study I think it would be a faster query if I searched for the match on the link_url entered in the form.  First thing should be a match with the link_url equals the alldomain.domain_name.

 

I rewrote your code, does this seem logical?

 

SELECT alldomain.domain_name
FROM alldomain WHERE alldomain.domain_name = '$link_url'
LEFT JOIN alldomain
    ON(alldomain.userid = register.id)
LEFT JOIN register
    ON(alldomain.userid = register.id)
$x_manual_approve = domain.manual_approve
$x_username = register.username

 

 

Thanks so much,

 

Gibs

[wildteen88]

The query I posted most probably wont work. I only posted an example SQL Query of how to query multiple tables using joins.

 

You cannot define (PHP) variables within SQL code. You first need to run the query using mysql_query and then retrieve the results using one of the mysql_fetch_* functions (eg mysql_fetch_assoc).

 

I rewrote your code, does this seem logical?

No. Because that is invalid MySQL syntax.

 

 

[gibsongk55]

ok thanks.  Let me play around with it and see what i can do.

 

 

Gibs