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

Php Execute Query returns wrong output – Phpmyadmin

I need help I have added filters to my code, they all are working fine but only month & year filter is creating confliction, although when I print the SQL in PHPMyAdmin I’m getting the desired results but not in HTML form.

Please check my code below

global $wpdb;

                if(isset($_GET['datepickervalue']) && !empty($_GET['datepickervalue'])){
                    $quotecreateddate = $_GET['datepickervalue'];
                }else{
                    $quotecreateddate = '';
                }
                if(isset($_GET['quotes_vehicle']) && !empty($_GET['quotes_vehicle'])){
                    $quotes_vehicle = $_GET['quotes_vehicle'];
                }else{
                    $quotes_vehicle = '';
                }

                if(isset($_GET['quotes_departure']) && !empty($_GET['quotes_departure'])){
                    $quotes_departure = $_GET['quotes_departure'];
                }else{
                    $quotes_departure = '';
                }
                if(isset($_GET['quotes_destination']) && !empty($_GET['quotes_destination'])){
                    $quotes_destination = $_GET['quotes_destination'];
                }else{
                    $quotes_destination = '';
                }

                if(isset($_GET['quote_number']) && !empty($_GET['quote_number'])){
                    $quote_number = $_GET['quote_number'];
                }else{
                    $quote_number = '';
                }

                if(isset($_GET['quotes_length']) && !empty($_GET['quotes_length'])){
                    $quotes_length = $_GET['quotes_length'];
                }else{
                    $quotes_length = 10;
                }
                $sql = "SELECT * FROM ". $wpdb->prefix ."quotes AS q LEFT JOIN ". $wpdb->prefix ."vehicles AS v ON q.vehicle = v.vehicle_id LEFT JOIN ". $wpdb->prefix ."departure AS dep ON q.departure_port = dep.departure_id LEFT JOIN ". $wpdb->prefix ."destination AS des ON q.destination_port = des.destination_id " ;
                $sql .= " WHERE form_id = 1 ";

                $total_query = "SELECT COUNT(id) FROM ". $wpdb->prefix ."quotes AS q LEFT JOIN  ". $wpdb->prefix ."vehicles AS v ON q.vehicle = v.vehicle_id LEFT JOIN ". $wpdb->prefix ."departure AS dep ON q.departure_port = dep.departure_id LEFT JOIN ". $wpdb->prefix ."destination AS des ON q.destination_port = des.destination_id ";

                $total_query .= " WHERE form_id = 1 ";




                if(isset($quotes_vehicle) && !empty($quotes_vehicle)){
                    $sql .= " AND q.vehicle = '".$_GET['quotes_vehicle']."' ";
                    $total_query .= " AND q.vehicle = '".$_GET['quotes_vehicle']."' ";
                }
                if(isset($quotes_departure) && !empty($quotes_departure)){
                    $sql .= " AND q.departure_port = '".$_GET['quotes_departure']."' ";
                    $total_query .= " AND q.departure_port = '".$_GET['quotes_departure']."' ";
                }
                if(isset($quotes_destination) && !empty($quotes_destination)){
                    $sql .= " AND q.destination_port = '".$_GET['quotes_destination']."' ";
                    $total_query .= " AND q.destination_port = '".$_GET['quotes_destination']."' ";
                }
                if(isset($quote_number) && !empty($quote_number)){
                    $sql .= " AND q.id LIKE '%".$_GET['quote_number']."%' ";
                    $total_query .= " AND q.id LIKE '%".$_GET['quote_number']."%' ";
                }
            if(isset($quotecreateddate) && !empty($quotecreateddate)){
                    $sql .= "AND DATE_FORMAT(q.created_at , '%Y-%m-%d' )  = '".$quotecreateddate."' ";
                    $total_query .= "AND DATE_FORMAT(q.created_at , '%Y-%m-%d' )  = '".$quotecreateddate."' ";
                }
                if(isset($quotes_length) && !empty($quotes_length)){
                    $sql .= " ORDER BY `id` DESC ";
                    $total_query .= " ORDER BY `id` DESC ";
                }

                echo $sql;
                if(isset($_GET['paged']) && !empty($_GET['paged'])){
                    $paged = $_GET['paged'];
                }else{
                    $paged = 1;
                }
                $total              = $wpdb->get_var( $total_query );
                $items_per_page     = $quotes_length; 
                $offset             = ( $paged * $items_per_page ) - $items_per_page;
                $sql .= " LIMIT ${offset}, ${items_per_page}";

                $getallquotes       = $wpdb->get_results( $sql);

The result I’m getting is same for all dates in HTML where I fetch using the foreach loop also the pagination count which is in total_query is showing correct but the SQL query is only showing last added entries for every dates

Thank you for help in advance

Tags:

2

Answers


  1. 0

    This may not solve the problem (and is not necessarily an answer), but you should consider trimming down your code:

    <?php
global $wpdb;

// Set all your empty variables here. No reason to use an else statement.
$quote_created_date = '';
$quotes_vehicle     = '';
$quotes_departure   = '';
$quotes_destination = '';
$quote_number       = '';
$quotes_length      = 10;


if ( isset( $_GET[ 'datepickervalue' ] ) && ! empty( $_GET[ 'datepickervalue' ] ) ) {
    $quote_created_date = esc_sql( $_GET[ 'datepickervalue' ] );
}

if ( isset( $_GET[ 'quotes_vehicle' ] ) && ! empty( $_GET[ 'quotes_vehicle' ] ) ) {
    $quotes_vehicle = esc_sql( $_GET[ 'quotes_vehicle' ] );
}

if ( isset( $_GET[ 'quotes_departure' ] ) && ! empty( $_GET[ 'quotes_departure' ] ) ) {
    $quotes_departure = esc_sql( $_GET[ 'quotes_departure' ] );
}

if ( isset( $_GET[ 'quotes_destination' ] ) && ! empty( $_GET[ 'quotes_destination' ] ) ) {
    $quotes_destination = esc_sql( $_GET[ 'quotes_destination' ] );
}

if ( isset( $_GET[ 'quote_number' ] ) && ! empty( $_GET[ 'quote_number' ] ) ) {
    $quote_number = esc_sql( $_GET[ 'quote_number' ] );
}

if ( isset( $_GET[ 'quotes_length' ] ) && ! empty( $_GET[ 'quotes_length' ] ) ) {
    $quotes_length = esc_sql( $_GET[ 'quotes_length' ] );
}

$sql = "SELECT * FROM " . $wpdb->prefix . "quotes AS q LEFT JOIN " . $wpdb->prefix . "vehicles AS v ON q.vehicle = v.vehicle_id LEFT JOIN " . $wpdb->prefix . "departure AS dep ON q.departure_port = dep.departure_id LEFT JOIN " . $wpdb->prefix . "destination AS des ON q.destination_port = des.destination_id ";
$sql .= " WHERE form_id = 1 ";

$total_query = "SELECT COUNT(id) FROM " . $wpdb->prefix . "quotes AS q LEFT JOIN  " . $wpdb->prefix . "vehicles AS v ON q.vehicle = v.vehicle_id LEFT JOIN " . $wpdb->prefix . "departure AS dep ON q.departure_port = dep.departure_id LEFT JOIN " . $wpdb->prefix . "destination AS des ON q.destination_port = des.destination_id ";

$total_query .= " WHERE form_id = 1 ";

// Now just check if your variables are empty or not.
if ( ! empty( $quotes_vehicle ) ) {
    $sql         .= " AND q.vehicle = '" . $quotes_vehicle . "' ";
    $total_query .= " AND q.vehicle = '" . $quotes_vehicle . "' ";
}
if ( ! empty( $quotes_departure ) ) {
    $sql         .= " AND q.departure_port = '" . $quotes_departure . "' ";
    $total_query .= " AND q.departure_port = '" . $quotes_departure . "' ";
}
if ( ! empty( $quotes_destination ) ) {
    $sql         .= " AND q.destination_port = '" . $quotes_destination . "' ";
    $total_query .= " AND q.destination_port = '" . $quotes_destination . "' ";
}
if ( ! empty( $quote_number ) ) {
    $sql         .= " AND q.id LIKE '%" . $quote_number . "%' ";
    $total_query .= " AND q.id LIKE '%" . $quote_number . "%' ";
}
if ( ! empty( $quote_created_date ) ) {
    $sql         .= "AND DATE_FORMAT(q.created_at , '%Y-%m-%d' )  = '" . $quote_created_date . "' ";
    $total_query .= "AND DATE_FORMAT(q.created_at , '%Y-%m-%d' )  = '" . $quote_created_date . "' ";
}

// No need to wrap this in an if statement since you are setting the value as 10 if there isn't a value.
$sql         .= " ORDER BY `id` DESC ";
$total_query .= " ORDER BY `id` DESC ";

echo $sql;
if ( isset( $_GET[ 'paged' ] ) && ! empty( $_GET[ 'paged' ] ) ) {
    $paged = $_GET[ 'paged' ];
} else {
    $paged = 1;
}
$total          = $wpdb->get_var( $total_query );
$items_per_page = $quotes_length;
$offset         = ( $paged * $items_per_page ) - $items_per_page;
$sql            .= " LIMIT ${offset}, ${items_per_page}";

$getallquotes = $wpdb->get_results( $sql );
    Login or Signup to reply.
  2. 0

    If you get a great result on phpMyAdmin, but not on the script itself, then do echo($sql); die($sql); and verify that the sql queries are the same.

    On the pages that call this page, verify, that the URL GET variables are URL encoded properly.

    Finally, the variables should be sanitized properly with something like mysql_real_escape_string(); so that no SQL injection attacks happen when this goes live.

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

Back To Top
Search