Str_Replace with query results

Question

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 robert@gmail.com. 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!

You may want to try something like [this](http://stackoverflow.com/a/15773754). Also if this is an assignment from school, you'll maybe atleast translate your code/columns to english to not get caught :p — HamZa, Jun 05 '13 at 07:22
what's the problem? if you have $Email assigned it will be changed when someone puts `[Email]` tag also there is no need to put variables in "" you can just use simply $var — Robert, Jun 05 '13 at 07:23
@HamZa Thnx for the link, ill take a look at it! And no this isn't a school assignment^^ And to Robert: I know this works now but I want to get it to work like I explained so it will keep working if I change the column names in the DB without having to edit my code. — Daanvn, Jun 05 '13 at 07:28
@Daanvn You mean something like [this](http://codepad.org/gKR2AsA8) ? — HamZa, Jun 05 '13 at 07:42
@HamZa something like that yeah, but how could I get the results from both my query's in the array like that? (haven't got much experience with arrays unfortunately) — Daanvn, Jun 05 '13 at 07:48
@HamZa Mysql now, I will change to PDO once this project is over because with this project it doesn't mind that I'm using Mysql. — Daanvn, Jun 05 '13 at 08:03
@Daanvn something like [this](http://codepad.org/56jLT6JH) ? (and you know the story about mysql being deprecated) — HamZa, Jun 05 '13 at 08:16
@HamZa It works! Thanks alot! If you post it as an answer I will accept it. And yes I know about the mysql deprication, thats why I'm switching to PDO soon. — Daanvn, Jun 05 '13 at 08:30

score 1 · Answer 1 · answered Jun 05 '13 at 08:34

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.

score 1 · Answer 2 · edited May 23 '17 at 12:11

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>";
}

Str_Replace with query results

2 Answers2