Welcome to WebmasterWorld Guest from 54.161.45.156

This page requires javascript to function properly. It seems that your browser does not have Javascript enabled. Please enable Javascript and press the Reload/Refresh button on your browser.

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

need help please putting together a mysql query in php

building mysql query to match form field with two tables and retrieve value

gibsongk55

6:35 am on May 19, 2011 (gmt 0)

New User

joined:Oct 28, 2006
posts: 22
votes: 0

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

coopster

2:58 pm on May 24, 2011 (gmt 0)

Administrator

coopster is a WebmasterWorld Top Contributor of All Time

joined:July 31, 2003
posts:12548
votes: 2

First, I am going to assume that the table 'domain' mentioned is actually 'alldomain'. If not, you are going to have to rework the information below to add an additional JOIN to the query.

OK, let's say that you have already scrubbed the user-supplied POST data and then pushed it through mysql_real_escape_string() to prepare it for use in your query and you have stored it in a variable called $linkurl. Next is to create your SQL statement ...

$sql =  
  "SELECT 
    alldomain.manual_approve, 
    register.username 
  FROM alldomain 
  INNER JOIN register ON(alldomain.userid = register.id) 
  WHERE 
    alldomain.domain_name = '$linkurl'" 
;

The INNER JOIN is the part that seems to avoid you. See if that works, test it and tweak it if necessary.

gibsongk55

12:27 am on May 25, 2011 (gmt 0)

New User

joined:Oct 28, 2006
posts: 22
votes: 0

Hi,

Thanks so much for the reply. After the query how do i get the two values moved into a variable say this:

$x_manual_approve = domain.manual_approve
$x_username = register.username

Thanks again,

Gibs

jspeed

4:40 pm on May 25, 2011 (gmt 0)

Junior Member

joined:Dec 13, 2007
posts:72
votes: 0

After coopsters code, you would run the query, and store the results in variables:

$result=mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
$x_manual_approve=$row["alldomain.manual_approve"];
$x_username=$row["register.username"];
}

Then use them as you need:

echo $x_manual_approve;

etc

gibsongk55

7:21 am on May 26, 2011 (gmt 0)

New User

joined:Oct 28, 2006
posts: 22
votes: 0

HI JS,

Thanks so much for the reply. I will try that. Sure is confusing reminds me of the old days with arrays and basic programming.

Thanks again,

Gibs

gibsongk55

8:53 am on May 26, 2011 (gmt 0)

New User

joined:Oct 28, 2006
posts: 22
votes: 0

Hi,

Thanks for the help guys. I still have a problem. The two fields I am trying to get from the database are coming up empty.

This is the code I have from you:

 $sql = 
 "SELECT 
 alldomain.manual_approve, 
 register.UserName 
FROM alldomain 
INNER JOIN register ON(alldomain.userid = register.Id) 
WHERE 
alldomain.domain_name = '$link_url'" 
;
$result=mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
$x_manual_approve=$row["alldomain.manual_approve"];
$x_username=$row["register.username"];
}

I am not understanding the beginning
SELECT alldomain.manual_approve, register.UserName

the above are two fields in two tables. Then FROM alldomain is only one table. Should that be FROM * ?

Thanks for help.

Gibs

coopster

12:06 pm on May 26, 2011 (gmt 0)

Administrator

joined:July 31, 2003
posts:12548
votes: 2

Read my first post again. The part that is escaping you is the JOIN. In order to retrieve values for columns from two different tables you need to join the tables together on their relative key, which in this case you mentioned was the userid. Column names are case-sensitive (looking at register.Username). You wrote it out twice now in two different cases. Check your error logs or dump the query upon error to see if you have issues. Something like this may be helpful during testing ...
$result = mysql_query($sql) or die(mysql_error());

gibsongk55

12:56 pm on May 26, 2011 (gmt 0)

New User

joined:Oct 28, 2006
posts: 22
votes: 0

Hi,

Yes i checked the database and that is why i changed the case to match it the field exactly. It is now correct and still the fields come up empty.

I pasted the code into the query in phpmyadmin and this is what I got:

SQL query: Documentation

$sql = "SELECT alldomain.manual_approve, register.UserName FROM alldomain INNER JOIN register ON(alldomain.userid = register.Id) WHERE alldomain.domain_name = '$link_url'";

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = 
             &quot;SELECT
              alldomain.manual_approve,
              regi' at line 1

Thanks

coopster

5:32 pm on May 26, 2011 (gmt 0)

Administrator

joined:July 31, 2003
posts:12548
votes: 2

You cannot paste PHP programming into phpMyAdmin. The query you want to paste to test is the "SELECT ..." part of the code shown here. It is wrapped in quotation marks to make it a string value. The string value is being assigned to the variable $sql. You can use that in your mysql_query() function as demonstrated by jspeed.

Also, $link_url is a variable too. You cannot just paste that into phpMyAdmin. You need to populate that with a value as I mentioned earlier.

gibsongk55

3:22 pm on May 27, 2011 (gmt 0)

New User

joined:Oct 28, 2006
posts: 22
votes: 0

Okay I still don't get it.

Okay as you said I copied your code:

SELECT 
  alldomain.manual_approve, 
  register.username 
 FROM alldomain 
 INNER JOIN register ON(alldomain.userid = register.id) 
 WHERE 
  alldomain.domain_name = 'www.example.com'

It returned one record:

manual_approve = 0

username = test@example.com

So that returns the correct value. But the code comes up empty at the end?

Thanks,

Gibs