将带有另一个搜索查询的变量添加到MySQL SELECT语句中

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!

Answer 1

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

1. Ternary Operators
2. MySQLI prepared statements
3. MySQLI bind param
4. urlencode
5. urldecode
6. htmlentities