Dynamically show SQL query on a PHP page on click, using AJAX

Question

Basically, I want to retrieve a certain product after clicking on an element. I'm using AJAX to pass the variable, and PHP to display the SQL query. I will use the product id on a WHERE statement, to retrieve and display the correct product.

Here is part of my code so far:

<html>
<head>
    <title>Left Frame</title>
<link href='http://fonts.googleapis.com/css?family=Indie+Flower' rel='stylesheet' type='text/css'>
<link href="stylesheets/main.css" rel="stylesheet" type="text/css">
<script src="javascripts/jquery-1.11.2.js">
</script>
</head>

<body>
<div class="container">

    <div id="bottomHalf">
        <img id="blank" src="assets/bottom_half.png" style= "z-index: 5" >
        <img src="assets/frosen_food.png"
        usemap="#Map2"
        border="0"
        id="frozen"
        style="z-index: 0;
        visibility:hidden;" >

        <map name="Map2">
            <area shape="rect" coords="7,95,126,146" alt="Hamburger Patties" href="#" id="hamburgerPatties">

     </div>
</div>
<script language="javascript">

  $("#hamburgerPatties").click(function(){
         var hamburgerPatties = "1002";
      $.ajax({
          type:"GET",
          url: "topRightFrame.php",
          data: "variable1=" + encodeURIComponent(hamburgerPatties),
          success: function(){
          //display something if it succeeds
          alert( hamburgerPatties );
      }
      });
  });

</script>
</body>
</html>

Part of my PHP code:

<?php

$product_id =(int)$_GET['variable1'];
$servername = "************";
$username = "************";
$password = "*************";
$dbname = "poti";
$tableName = "products";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM $tableName ";
$result = $conn->query($sql);

// Display all products on the database
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Product ID: " . $row["product_id"]. " - Product Name: " . $row["product_name"]. " Unit Price:" . $row["unit_price"]. "<br>";
    }
} else {
    echo "0 results";
}

    // Display product I clicked on the other frame
    if (isset($GET['variable1'])) {

    $sql = "SELECT * FROM $tableName WHERE product_id = " . $_GET['variable1'];
    $result = $conn->query($sql);

        if ($result) {
        echo '<table>';

            while ($row = $result->fetch_assoc())
            {
            echo '<tr>';
            echo '<td>', "Product ID: " . $row["product_id"]. " - Product Name: " . $row["product_name"]. " Unit Price:" . $row["unit_price"]. "<br>";
            echo '</tr>';
            }
        echo '</table>';
        }
}
$conn->close();
?>

I'm able to display all the products. But starting from the ifsset statement, the code no long works. I get no error message or anything. How can I solve this? I'm pretty new to PHP.

EDIT: Ok, I managed to get the product I want when I hard code the product id. Now I need to get this variable using javascript.

Artur Filipiak · Accepted Answer · 2015-04-12 07:14:43Z

1

You have syntax errors in your if statements.

When you're setting if, you should enclose the statement in braces:

instead of if something : do if(something): and then end it with endif; (when using colons :)

// Display product I clicked on the other frame
if (isset($GET['variable1'])):
    $query = "SELECT * FROM $tableName WHERE product_id = " . $_GET['variable1'];
    $result = mysqli_query($conn, $query);
    if ($result):
        echo '<table>';
        while ($row = $result->fetch_assoc()) {
            echo '<tr>';
            echo '<td>', $row['product_id'], '</td>'; // and others
            echo '</tr>';
        }
        echo '</table>';
    endif;
endif;

Or, use {} braces instead of colons :

// Display product I clicked on the other frame
if (isset($GET['variable1'])){
    $query = "SELECT * FROM $tableName WHERE product_id = " . $_GET['variable1'];
    $result = mysqli_query($conn, $query);
    if ($result){
        echo '<table>';
        while ($row = $result->fetch_assoc()) {
            echo '<tr>';
            echo '<td>', $row['product_id'], '</td>'; // and others
            echo '</tr>';
        }
        echo '</table>';
    }
}

answered Apr 12, 2015 at 7:14

Artur Filipiak

9,1874 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Filipe Barreto Peixoto Teles Over a year ago

Changed those and I few other things and still no go.

Artur Filipiak Over a year ago

You didn't read my answer carefully. You still have if isset($GET['variable1']) { . You should enclose isset with () braces too, like if (isset($GET['variable1'])) {

Filipe Barreto Peixoto Teles Over a year ago

Sorry, I didn't notice that. Now the code is running, and it displays all the data, but nothing after clicking the product element.

Artur Filipiak Over a year ago

I don't see any product element in your code. And what is the PHP in your question, is it part of a topRightFrame.php file? Does console says anything?

Filipe Barreto Peixoto Teles Over a year ago

Updated the code, the product element in this case is the hamburgerPatties. Yep, is the topRIghtFrame.php file. No errors on my console.

Ali Sheikhpour · Accepted Answer · 2015-04-12 09:05:29Z

0

You have syntax error in if block. use {} for if block also use echo instead of pure html tag(table). This error prevents successful state of ajax request. also don't forget to close table.

if ($result)
{
 echo '<table>';

 while ($row = $result->fetch_assoc()) 
   {
    echo '<tr>';
    echo '<td>', $row['product_id'], '</td><td>'; // and others
    echo '</tr>';

  }
 echo '</table>';
}

and the ajax code to display php result instead of test alert would be this:

<script language="javascript">

  $("#hamburgerPatties").click(function(){
         var hamburgerPatties = "1002";
      $.ajax({
          type:"GET",
          url: "topRightFrame.php",
          data: "variable1=" + encodeURIComponent(hamburgerPatties),
          success: function(data){
          alert(data);
      }
      });
  });

</script>

edited Apr 12, 2015 at 9:05

answered Apr 12, 2015 at 7:09

Ali Sheikhpour

11.4k5 gold badges53 silver badges100 bronze badges

9 Comments

Ali Sheikhpour Over a year ago

what happens if you open php page directly when passing variable1 as query string?

Filipe Barreto Peixoto Teles Over a year ago

I manage to retrieve the product I want when I hard code the ID. Edited the working code. Now I need to be able to retrieve this value using ajax, instead of manually inputting it.

Ali Sheikhpour Over a year ago

OK! I have updated the answer including jquery to show data instead of fake alert.

Filipe Barreto Peixoto Teles Over a year ago

When I do that, I get an alert displaying the source code of my PHP page. Is it the output I should expect? If I add echo $product_id, I get the correct product in this alert. But still, nothing happens at the PHP page, it stays static, and the SQL query doesn't run.

Ali Sheikhpour Over a year ago

what if you convert variable1 to number to make sure there is no data type problem in your sql query. I mean : where productId=".intval($_GET['variable1'])

|

Collectives™ on Stack Overflow

Dynamically show SQL query on a PHP page on click, using AJAX

2 Answers 2

5 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related