adding a variable with another search query into a MySQL SELECT statement

Question

@OhGodWhy: thanks again for your help. I'm now showing you exactly how my code looks like, maybe you have an idea what could be wrong:

Here, in the first section, I replaced my <a href> part with the first block (two lines) of your answer. See code below:

//-query the database table
$sql="SELECT * FROM Hashtags";

//-run the query against the mysql query function
$result=mysql_query($sql);

//-create while loop and loop through result set 
while($row=mysql_fetch_array($result)){

//-display the result of the array
$query_string = 'hashtag=true&tag='.urlencode($row['Hashtag']);
echo '<a href="index.php?'.htmlentities($query_string).'" title="Suche nach '.$row['Hashtag'].'">#'.$row['Hashtag'].'</a>';

Then, I added the second block of your answer right after the start of my hashtags function. I wrapped the if-statement around the whole function, until the end of the while-part. See below:

function hashtags() {

$tag = isset($_GET['tag'])? urldecode($_GET['tag']) : false ;
if($tag) {

$mysqli = new mysqli('host', 'user', 'pass', 'db');
$stmt = $mysqli->prepare("select * from table where name like CONCAT('%', ?, '%')");
$stmt->bind_param('s', $tag);
$stmt->execute();

//-run the query against the mysql query function
$result=mysql_query($sql);

//-create while loop and loop through result set 
while($row=mysql_fetch_array($result)){

//-display the result of the array
echo '...'
//end of while & if

Does the while-loop have to be adjusted to mysqli as well? maybe this information can help: In my browser the URL looks right: "index.php?hashtag=true&tag=..."

When I click on the <a href> I get an empty screen.

Thanks again for your help and sorry for bothering you!

`click` and then `$_GET['tag']`. But before trying that sort of thing, you should read up about [sql injection attack](http://bobby-tables.com) vulnerabilities. — Marc B, Nov 10 '14 at 19:03
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). You will also want to [Prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 10 '14 at 19:04
thanks for your advice! @MarcB: But I'm not sure if I understand your answer. Could you maybe show me how/where you would put this? thank you!! — Frank, Nov 10 '14 at 19:11

score 2 · Accepted Answer · edited May 23 '17 at 12:15

2

You should just provide the hash tag as a urlencoded string that contains the value of $row['hashtag']

$query_string = 'hashtag=true&tag='.urlencocde($row['hashtag']);
echo '<a href="index.php?'.htmlentities($query_string).'" title="Suche nach '.$row['Hashtag'].'">#'.$row['Hashtag'].'</a>';

Then in your function hashtags, you can grab the tag value like this:

$tag = isset($_GET['tag'])? urldecode($_GET['tag']) : false ;
if($tag):

Furthermore, you need to move away from mysql and secure yourself from SQL injection. We can do that all by migrating to the mysqli library, and using prepared statements.

$mysqli = new mysqli('host', 'user', 'pass', 'db');
$stmt = $mysqli->prepare("select * from table where name like CONCAT('%', ?, '%')");
$stmt->bind_param('s', $tag);
$stmt->execute();

while($row = $stmt->fetch_assoc()){
    //echo stuff
}

You are required to concat the LIKE otherwise you will get errors.

Resources

edited May 23 '17 at 12:15

Community

1
1

answered Nov 10 '14 at 19:12

Ohgodwhy

49,779
11
80
110

thanks a lot! Just one thing, as I haven't been able to get it to work yet: I added the second block of your code to my hashtags function, but I'm not sure how to use the if-statement at the end of this block? do you mean if($tag) { connect to database... } ? and one more thing: The new SELECT-statement should look like this? "SELECT * from table where NAME like '%".$query_string."%' ? thanks!! – Frank Nov 10 '14 at 19:42
@sehetmich Oh sorry there's alternative ways to write that, such as `if(condition): //condition... else;` but yes, `if(condition){ }` is fine as well. – Ohgodwhy Nov 10 '14 at 19:44
sorry, sent the comment before completing it! – Frank Nov 10 '14 at 19:46
@sehetmich The new select statement should look exactly like I've written it, you shouldn't need to change anything in that last block of code. :) – Ohgodwhy Nov 10 '14 at 19:51
@sehetmich Have a look at how to use the while loop given the architecture of the code above. :) – Ohgodwhy Nov 10 '14 at 20:47
you're great! thank you, now everything works as it should :) – Frank Nov 10 '14 at 20:48

adding a variable with another search query into a MySQL SELECT statement

1 Answers1