MySQL query with PHP

Question

I'm self teaching myself to do MySQL and PHP. It is a new world to me, i have a couple of books and i do a lot of research on google before asking here. So I'm stuck with the following problem and I can't find an answer, I have a MySQL database, the database has a specific field called URL.

Part of the URL field looks like this:

&path=products&user=Mark24&id=2616

currently i have it set up so people can search for items in the database, and that works great. what i want to be able to add to the search feature is search by user. as you can see the user is in the middle of the entire url so i have this:

    $query = "select Productid, Productname, url,  from Products where Productid like '%$keyword%' or Productname like '%$keyword%' or url like '%$keyword%' ";
    $result = mysqli_query($dbc,$query);

Now comes the real issue, ^^ this works wonders, people can actually search for the username and it will display exactly what i want. However if people search for the word "products" or "path" which is also in the url they will get over 2000 matches because every single url in my database has the word products and path. Why I want to avoid that? i don't want customers looking up our entire database, I'm also afraid of server overload because with each query a customer gets an image of the product, along with the product name and the link to the product. trying to load over 2000 pictures also slows down or crashes my browser, so i need to fix this somehow, but i'm not sure how to tackle it.

So is there a way to match only the part that says user="blah" for the URL field? do i do this with PHP, preg-match perhaps???

Any information provided is highly appreciated! Thanks.

Answer 1

just search for

"%&path=products&user=%what you're searching for%"

that way you'll get returned only those rows that have 'product' apart from your path=product parameter. Alternatively, databases like mysql, postgresql and others allow you to use regular expressions as well but that's more costly in terms of processing power.

in regular expression form it would look somewhat like this; with php variable in the middle:

"... WHERE url REGEXP  '&path=products&user=" . $user . "&id=[0-9]+$'"

if you use something like this, then you would need to protect your inputted value against sql injection by escaping it though. You should start to have a look at pdo for making database fueled applications and use prepared statements with bound parameters to safeguard yourself. You'd basically would just need to construct your regular expression and pass it as the parameter to bind.

Answer 2

Try this instead:

$query = "select Productid, Productname, url,  from Products where Productid like '%$keyword%' or Productname like '%$keyword%' or url like '%user=$keyword%' ";

Answer 3

Why not make two more columns? One containing the username and containing the id? And then when you do your search, search in those instead.