phpmyadmin Question posted in
The official documentation can be found here.

Why is using LIKE %value% giving my php page a 500 code? – Phpmyadmin

I have tried my best to google this issue and I think maybe I am just doing something wrong that I don’t understand? I have a small PHP page that generates some JSON for me from a database for use in another system.

It works perfectly until I try and use LIKE in my SQL statement with the % wild card. The same query works perfectly testing in phpMyAdmin. I am a little confused.

my data looks something like this.

shop, region, mangoes, sales, more data.

I am trying to filter by region. Region currently has four values. Let’s call them “My shop”, “Clearance shop”, “Franchise” and “Special”

I want to be able to eventually do a URL like data.php?ShopType=shops. this would bring up my query and filter for all regions with “shop” in the region

easy I thought. I constructed a quick test query.

SELECT * FROM `RetailSalesData` WHERE `Date` = '01/07/2019' AND `Region` LIKE '%shop%'

this works as expected in phpmyadmin but when I used it on my test server it was throwing a 500.

I was using $_GET to grab my values for filtering my query so I removed and commented that out and just tried the straight query in case I was going wrong there but still no luck.

if I do something like 

"SELECT * FROM `RetailSalesData` WHERE `Date` = '01/07/2019' AND `Region` LIKE 'My shop'"

then it works. But then I might as well not be using LIKE and I don’t get all the results I want because I am not using the % wildcard. It seems like I can’t use “%” in the query in PHP and I don’t understand why.

<?php
//setting header to json
header('Content-Type: application/json');

$servername = "server";
$username = "User";
$password = "password";
$dbname = "mangoes";

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

//get the data range and type
//$ShopType = $_GET["ShopType"];

//query to get data from the table
$query = sprintf("SELECT * FROM `RetailSalesData` WHERE `Date` = '01/07/2019' AND 'Region' LIKE '%shop%'");

//execute query
$result = $conn->query($query);

//loop through the returned data
$data = array();
foreach ($result as $row) {
    $data[] = $row;
}

//free memory associated with result
$result->close();

//close connection
$conn->close();

//now print the data
print json_encode($data);
?>

From the above code, I expect a list of my shops with their respective data filtered out for the shops in “regions” with “shop” in the region values. What I get is a 500 error page if my SQL query contains “%” wildcard using LIKE.

Tags:

3

Answers


  1. 0

    don’t use quote around column name and use a proper conversion for string date 

            $query = sprintf("SELECT * FROM `RetailSalesData` 
            WHERE `Date` = str_to_date('01/07/2019' ,'%d/%m/%Y')
             AND Region LIKE concat('%', shop,'%')";
    Login or Signup to reply.
  2. 0

    In your query you’re using:

    LIKE = ‘%shop%’

    however, you’re passing that in sprintf function which has a special purpose for %s chars.
    %s means that you’ll provide the function with a string argument for sprintf to put in place of %s and since you’re not providing the argument, it’s giving 500 error.

    You can get more information on sprintf on:
    https://www.php.net/manual/en/function.sprintf.php

    Login or Signup to reply.
  3. 0

    sprintf have the issue and also you’re passing ‘region’ as string.. and looks like this is column name

    use this code.

    <?php
//setting header to json
header('Content-Type: application/json');

$servername = "server";
$username = "User";
$password = "password";
$dbname = "mangoes";

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

//get the data range and type
//$ShopType = $_GET["ShopType"];

//query to get data from the table
$query = "SELECT * FROM `RetailSalesData` WHERE `Date` = '15/07/2019' AND Region LIKE '%shop%'";

//execute query
$result = $conn->query($query);
$data1 = $result->fetch_all();
//loop through the returned data
$data = array();
foreach ($data1 as $row) {
    $data[] = $row;
}

//free memory associated with result
$result->close();

//close connection
$conn->close();

//now print the data
print json_encode($data);
?>

    Also you are not fetching the data and looping through the result.. updated the code to fix it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search