Modifying Value in PHP/MySQL Database Query

Question

I know the solution to this question is super simple, but I'm missing something.

I have a database filled with people's names. Most will be displayed @ URL's like this:

MySite/People/Abraham_Lincoln

However, people who are only important in Washington State will be displayed @ URL's like this:

MySite/People/Washington/Gary_Locke

Abraham Lincoln is simple because the value in the database table, field URL (Abraham_Lincoln), matches the URL (Abraham_Lincoln). However, the database value Gary_Locke does NOT match the URL Washington/Gary_Locke.

So I'm trying to modify my query so that if a person is associated with Washington (WHERE Site = 'WA'), the webpage URL ($MyURL) won't equal the database table field URL but Washington/ + URL.

But how do I append 'Washington/' and URL? The following query returns 0 results when I test it in SQL, and it doesn't work at all in my PHP page.

$result = mysql_result(mysql_query("SELECT COUNT(URL)
FROM people
WHERE URL = '$MyURL' AND Site = 'PX'
OR '$MyURL' = 'Washington/'URL AND Site = 'WA'"),0);

So, again, if I'm focusing on Gary Locke (Gary_Locke in field URL), then I want to be able to display information about him at MySite/People/Washington/Gary_Locke, NOT MySite/People/Gary_Locke.

Can anyone tell me what I'm doing wrong? Thanks.

bumperbox · Accepted Answer · 2013-08-12 06:32:15Z

1

Concat will join the Washington to URL see below

$result = mysql_result(mysql_query("SELECT COUNT(URL)
FROM people
WHERE URL = '$MyURL' AND Site = 'PX'
OR '$MyURL' = CONCAT('Washington/', URL) AND Site = 'WA'"),0);

Can I also suggest doing it this way, so that if you have an sql error, it will display the error, so you can tell what is wrong

$res = mysql_query("SELECT COUNT(URL)
FROM people
WHERE URL = '$MyURL' AND Site = 'PX' 
OR '$MyURL' = CONCAT('Washington/', URL) AND Site = 'WA'");

if (!$res) {
    die('Invalid query: ' . mysql_error());
}

$result = mysql_result($res, 0);

I also hope you are escaping your parameters, this will do the trick. (search for sql injection if you are not sure why that is a good idea)

$MyURL = mysql_real_escape_string($MyURL);

And finally, the mysql_ set of functions will be removed from the latest version of php very soon. You should look at mysqli or pdo if this is a long term project.

edited Aug 12, 2013 at 6:32

answered Aug 12, 2013 at 6:18

bumperbox

10.2k6 gold badges46 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2469520 Over a year ago

Wow, thanks for all the tips; you solved several things. The second query, with error reporting, isn't working for me, but I'll see if I can find out what's going on. In the meantime, the first query works great. I've heard about PDO, but I'm not happy to hear that PHP is going to scuttle MySQL functions soon. I haven't had time to learn PDO yet.

bumperbox Over a year ago

I have tweaked the error checking code slightly, see if that works for you.

Collectives™ on Stack Overflow

Modifying Value in PHP/MySQL Database Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related