Welcome to WebmasterWorld Guest from 23.22.220.37

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

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

New User

5+ Year Member

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
2:58 pm on May 24, 2011 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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.
12:27 am on May 25, 2011 (gmt 0)

New User

5+ Year Member

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
4:40 pm on May 25, 2011 (gmt 0)

Junior Member

5+ Year 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
7:21 am on May 26, 2011 (gmt 0)

New User

5+ Year Member

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
8:53 am on May 26, 2011 (gmt 0)

New User

5+ Year Member

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
12:06 pm on May 26, 2011 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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());
12:56 pm on May 26, 2011 (gmt 0)

New User

5+ Year Member

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 =
"SELECT
alldomain.manual_approve,
regi' at line 1


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

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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.
3:22 pm on May 27, 2011 (gmt 0)

New User

5+ Year Member

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
5:47 pm on May 28, 2011 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


But the code comes up empty at the end?


Meaning? The correct result set was returned so are you not printing it out correctly or something else? You'll have to offer more details.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members