CODEWITHSUNDEEP
phpstr-replace
Daanvn
Daanvn

Reputation: 1266

Str_Replace with query results

I have a MySql DB and in the Table 'Klant' I have the column names:

ID
Naam
Email
Soort
Status

I get the column names with this query:

$strSQL = "select column_name from information_schema.columns where table_name='Klant'";

And I am selecting the data from the Table with this simple query:

$strSQL1    = "SELECT * FROM NAW.Klant";

What I want to do is search a text and with str_replace I want to replace the column_names with the data from the DB. For example:

If I type in Hello Naam, your email adress is Email I would want it to display Hello Robert your email adress is [email protected]. And I will put that in a loop to do it for every row. I am currently using this:

$ID         = $row['Klant_ID'];
$Naam       = $row['Naam'];
$Email      = $row['Email'];
$Soort      = $row['Soort'];
$Naam       = $row['Status'];
$vaaw       = array("[ID]","[Naam]", "[Email]", "[Soort]", "[Status]");
$vervang    = array("$ID","$Naam", "$Email", "$Soort", "$Status");

echo str_replace($vaaw, $vervang, $message);

The reason I do not want to use this anymore is because if I ever need to change/add/delete a column the code would still work. (I know it is a bad idea to change columns but you never know.) And also this code will work with other Tables/DB's to.

I have tried loads of things to get this to work but I just haven't got a clue how to do this and it has been bugging me for almost 2 days now. If someone knows a function or a way to do this it would be very helpful!

Upvotes: 1

Views: 1517

Answers (2)

Daanvn
Daanvn

Reputation: 1266

I solved it using the script that HamZa linked in the comments. Since he is not posting it as an answer I will do it myself because I think it could help others.

The code that solved the problem is this:

$connection = mysql_connect('localhost', 'root', 'pw') or die('couldn\'t connect to the database.<br>'. mysql_error());
mysql_select_db("NAW");
$strSQL1    = "SELECT * FROM Klant";
$result = mysql_query($strSQL1, $connection) or die('Something went wrong with the query.<br>'. mysql_error());
while($row = mysql_fetch_assoc($result)){
    $text = $_POST['naam'];
    foreach($row as $k => $v){
        $text = str_replace('['.$k.']', $v, $text);
    }
    echo $text;
    echo "<br>";
}

Upvotes: 1

Bere
Bere

Reputation: 1747

Try this:

<?php
$strSQL = "select column_name from information_schema.columns where table_name='Klant'";

$con=mysqli_connect('host', 'username', 'password', 'db');

if(!$con){
    //error    
}

$result=mysqli_query($con,$strSQL);

if(!$result){
    //error
}

$table_columns=array();
//$row=mysqli_fetch_assoc($result);
while($row=mysqli_fetch_assoc($result))
{
    $table_columns[]=$row['column_name'];
}

$query="select * from NAW.Klant "; //limit 10";

$result=mysqli_query($con,$query);

if(!$result){
    //error
}

$greeting_text="";

while($row=mysqli_fetch_assoc($result)){
    $greeting_text.= (isset($row['naam']))? "Hello {$row['naam']}":""; // because you want the 'hello'
    for($i=1;$i< count($table_columns);$i++){
        $greeting_text.=" Your ".$table_columns[$i]." is ".$row[$table_columns[$i]].", "; 
    }
   $greeting_text.="\n";
}
echo $greeting_text; //test your result

If you have a predefined string template (to be replaced by column names or their values), you need to change that code when there is any change in the table columns. I simply choose to dynamically generate the string depending on the availability of columns. But if you need to use a predefined string, it is not difficult to do so.

Upvotes: 1

Related Questions