homepage Welcome to WebmasterWorld Guest from 107.20.25.215
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
need help please putting together a mysql query in php
building mysql query to match form field with two tables and retrieve value
gibsongk55




msg:4314516
 6:35 am on May 19, 2011 (gmt 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




msg:4316909
 2:58 pm on May 24, 2011 (gmt 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.

gibsongk55




msg:4317209
 12:27 am on May 25, 2011 (gmt 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




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




msg:4317788
 7:21 am on May 26, 2011 (gmt 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




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




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

gibsongk55




msg:4317893
 12:56 pm on May 26, 2011 (gmt 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

coopster




msg:4318103
 5:32 pm on May 26, 2011 (gmt 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.

gibsongk55




msg:4318624
 3:22 pm on May 27, 2011 (gmt 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

coopster




msg:4319025
 5:47 pm on May 28, 2011 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved