Send SQL concat to PHP variable

Question

I am trying to select multiple columns with concat an put the returned data into one textbox.

I think there is something wrong with my definition for the variables. But I could not figured out what is wrong. Here are the variables:

$id = isset($_POST['id'])?$_POST['id']:'';
$name = isset($_POST['firstname'])?$_POST['firstname']:'';
$name .= isset($_POST['insertion'])?$_POST['insertion']:'';
$name .= isset($_POST['lastname'])?$_POST['lastname']:'';

When I define just one variable for $name the script works. But that is not what I want.

Does someone know what is wrong?

Here is the other part of my script.

First I have a textbox. The data needs to be send to this textbox:

<input type="text" class="form-control" id="name" name="name" placeholder="Name">

The button calls sends '5' as the ID and runs the script getName():

<button type="button" rel="5" onclick="getName();"

<script type="text/javascript">
  $('body').on('click', '.selectClass', function () {
    var id         = $(this).attr('rel');
    $("#id").val(id);
    modal.style.display = "none";
  });     
</script>

After clicking on the button the id is deployed here:

<input type="text" class="form-control" id="id" name="id">

The onClick event runs the following script:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script> 
  function getName(value) { // Do an Ajax request to retrieve the product price 
    console.log("getName before ajax", jQuery('#id').val());
      jQuery.ajax({ 
        url: './get/getname5.php', 
        method: 'POST', 
        data: {'id' : jQuery('#id').val()},
        success: function(response){ 
        console.log("getName after ajax", jQuery('#id').val());
        jQuery('#name').val(response);
      }, 
      error: function (request, status, error) { 
        alert(request.responseText); 
      }, 
    });                 
  } 
</script>

The jquery script calls the PHP, which is not working with the multiple variables for $name

<?php    
  session_start();
  $servername = "localhost";
  $username = "root";
  $password = "";
  $dbname = "db";

  $conn = new mysqli($servername, $username, $password, $dbname) ;
  if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error) ;
  }else {
    $id = isset($_POST['id'])?$_POST['id']:'';
    $name = isset($_POST['firstname'])?$_POST['firstname']:'';
    $name .= isset($_POST['insertion'])?$_POST['insertion']:'';
    $name .= isset($_POST['lastname'])?$_POST['lastname']:'';

    $query = 'SELECT concat(firstname, ' ', insertion, ' ', lastname) as name FROM users WHERE id="' . mysqli_real_escape_string($conn, $id) . '"';    
    $res = mysqli_query($conn, $query) ;
    if (mysqli_num_rows($res) > 0) {
      $result = mysqli_fetch_assoc($res) ;
      echo $result['name'];   
    }else{
      $result = mysqli_fetch_assoc($res) ;
      echo $result['name']; 
    }
  }
?>

`$name = isset($_POST['firstname'])?$_POST['firstname']:''; $name .= isset($_POST['insertion'])?$_POST['insertion']:''; $name .= isset($_POST['lastname'])?$_POST['lastname']:'';` What is the purpose of this code? I ask because: 1. There are no POST variables with those names which get sent to the server in your ajax request. 2. You never use the resulting $name variable for anything. — ADyson, Jun 30 '17 at 09:54
I thought the post is for the SQL query. In that case the variable: `$name = isset($_POST['name'])?$_POST['name']:'';` should work, right? — John, Jun 30 '17 at 10:03
no. Your ajax never sends a variable called name, or firstname, or insertion or lastname: `data: {'id' : jQuery('#id').val()}` sends only ID, which you then use in your SQL query correctly. The PHP $name variable is never used for anything, even if it did actually contain any content. Anyway if the idea is to _output_ the name which relate to the ID, why would you want to _input_ a name to the server? Makes no sense. You seem to have got confused between input and output — ADyson, Jun 30 '17 at 10:07
Having said that, all that code is redundant, but it looks like it shouldn't stop the rest of it from working and returning the name from the database into the textbox. That bit should be working, yes? — ADyson, Jun 30 '17 at 10:14
You are right. In that case there is no need for $name in the PHP script. But the script is still not working — John, Jun 30 '17 at 10:16
ok, so define "not working". Where does it fail? Does it send "id" to the server correctly? Does it return a name from the database correctly in the response? Do you get any error messages either from PHP or from Javascript (check your browser's console and network tab in the developer tools)? — ADyson, Jun 30 '17 at 10:17
BTW is the "id" column in your database a varchar or an int? Because you're sending the id variable to it inside quotes, like a string/varchar. If the column type is int, it won't match any rows because it tries to compare a string to a number. You should in any case research how to use parameterised queries, which both avoids this kind of problem and also properly protects you from SQL Injection attacks (mysqli_real_escape_string can do some things but it's not as safe as using parameters). If it _is_ a varchar, I think mysql prefers single quotes not double around strings. — ADyson, Jun 30 '17 at 10:19
And this: `if (mysqli_num_rows($res) > 0) { $result = mysqli_fetch_assoc($res) ; echo $result['name']; }else{ $result = mysqli_fetch_assoc($res) ; echo $result['name']; }` makes no sense either. In the `else` condition, there are no rows you can't fetch any data or return a value. You need to return null or maybe throw an exception, if appropriate. At the moment the if and else blocks both try to do the same thing, which is obviously not going to work, otherwise there would be no point having an if. — ADyson, Jun 30 '17 at 10:23
In the console log I see that the POST for id is empty `id=`. When I manually edit this I see in the console log that `id=5`. But It still gives a internal server error — John, Jun 30 '17 at 10:26
that'll be your problem then. Remove the " " around the id value in the SQL. And then find out how to use parameters anyway, for future reference :-) — ADyson, Jun 30 '17 at 10:26
hm ok that's odd, it should send the value correctly to the server with that code, I think. — ADyson, Jun 30 '17 at 10:27
I removed the " " around the id but I still get the same error — John, Jun 30 '17 at 10:30
well if it's not passing ID through to the server in the first place as you suggest, then you will. What's the actual error your're getting? (Not "internal server error", the actual PHP error (it may be reporting it in the response body, or you can use the server to log it). I will have a think why it might not be reading the value. — ADyson, Jun 30 '17 at 10:32
does this: `console.log("getName before ajax", jQuery('#id').val());` show you the expected "5" value for `jQuery('#id').val()`? — ADyson, Jun 30 '17 at 10:37
When I manually write `5` in the textbox `id` it will send `id=5`. But the response is empty. The only error I can see is the error: `Failed to load resource: the server responded with a status of 500 (Internal Server Error)`. BTW the script works when I change `select concat(firstname, ' ', insertion, ' ', lastname) as name` to `select firstname` — John, Jun 30 '17 at 10:38
There should be some more detail if you look inside the actual body of the ajax call (click on the request in your browser's network tab). BTW You seem to be using `concat` correctly - see http://www.w3resource.com/mysql/string-functions/mysql-concat-function.php . Are you running MySQL 5.6 or above? Does the query work if you run it directly in mySQL? — ADyson, Jun 30 '17 at 10:41
As for having to manually write 5 in the textbox, I'm pretty sure this is because ` $('body').on('click', '.selectClass', function () { var id = $(this).attr('rel'); $("#id").val(id); modal.style.display = "none"; }); ` never runs. There's no button with the "selectClass" class in your code. Certainly the button with the "rel=5" does not have it. And also if you did have it, it might execute _after_ the getName() function which would be no good. Simpler to render your button as ` — ADyson, Jun 30 '17 at 10:43
and use that ID variable to send to the server, rather than proxying it via a textbox. — ADyson, Jun 30 '17 at 10:43
Yes, the query works in when I run it in mysql. In PHP I can see the following: `libmysql - mysqlnd 5.0.11-`. So I think it must be version 5.0.11. — John, Jun 30 '17 at 10:49
no that's the version of the mysqlnd module. You need the version of the mySQL _server_. https://www.liquidweb.com/kb/how-to-check-the-mysql-version/ But if the query works in mySQL then it shouldn't matter. — ADyson, Jun 30 '17 at 10:50
concat should work fine then. Are sure that's the reason the query fails? Like I said, try and look for the real PHP underlying error in your network tab. If it crashed, it likely dumped it as the response to the ajax request. You can always switch on PHP detailed error reporting if it didn't. https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display — ADyson, Jun 30 '17 at 10:55
I have created 2 get files: `getname5.php` and `getname6.php`. The first file uses concat and the second file selects only the firstname. That is the only difference in the script. When I open `getname5.php` in my browser I get a browser error: `This page isn’t working`. For the second file `getname6.php` I dont get any error. So I think the reason must be concat, because that is the only thing that is different — John, Jun 30 '17 at 11:08
Only thing I see in the network tab is that `getname5.php` is red. — John, Jun 30 '17 at 11:11
I've just realised what it is: `'SELECT concat(firstname, ' ', insertion, ' ', lastname)` - the first `'` inside the query is terminating the PHP string and probably causing a PHP syntax error. Use `"` to build the PHP string, allowing you to have `'` inside it without it getting confused. — ADyson, Jun 30 '17 at 11:11
So I need to change it to: `(firstname, insertion, lastname)`. Right? — John, Jun 30 '17 at 11:15
no, that's not what I said. Read it again. Use " " to start and end your PHP string. Then you can keep the 's inside it. e.g. `$query = "SELECT concat(firstname, ' ', insertion, ' ', lastname) as name FROM users WHERE id=" . mysqli_real_escape_string($conn, $id);` — ADyson, Jun 30 '17 at 11:16
Now I have `$id = isset($_POST['id'])?$_POST['id']:'';` and `$query = "SELECT concat(firstname ' ', insertion, ' ', lastname) as name FROM users WHERE id=" . mysqli_real_escape_string($conn, $id)" AND user_id=2";`. It still gives the error — John, Jun 30 '17 at 11:21
`mysqli_real_escape_string($conn, $id)" AND` You missed a `.` between `)` and `"` to concatenate the strings. If you had the PHP error reporting switched as I suggested earlier you could spot simple syntax errors like this much more easily because it would give you a clue in the error message. — ADyson, Jun 30 '17 at 11:52
Sorry. I mistyped it in comment. In my script it is `id=" . mysqli_real_escape_string($conn, $id) . " AND` — John, Jun 30 '17 at 11:55
I get 2 warnings: `Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /getname5.php on line 23` and `Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /getname5.php on line 27`. But this cant be the reason right? — John, Jun 30 '17 at 12:01
no, those are only warnings. Although they may indicate that mysqli_query is returning "false" which could indicate the query failed. — ADyson, Jun 30 '17 at 12:02
whether you're a guest or not has nothing to do with your browser potentially caching the ajax response or the server caching the script. — ADyson, Jun 30 '17 at 12:02
that's great. Hopefully you learned something about the steps you can take to debug your code :-) Next time, you can do this before you ask the question, and either fix it yourself, or be able to give a precise error message in the question details. — ADyson, Jun 30 '17 at 12:06

Send SQL concat to PHP variable

0 Answers0