MySQL query from query in PHP

Question

I am trying to find the most efficient way to query the results of a query and every one of the solutions I am finding online produce a 1064 error. I don't understand what I am doing wrong.

The latest iteration I have tried is the following:

$sql = "SELECT keys.* FROM (SELECT * FROM calendar WHERE event_key LIKE '".$eventKey."%') keys WHERE event='".$eventTitle."'";

The previous one I tried was:

$sql = "SELECT *(events) FROM (SELECT * AS events FROM calendar WHERE event_key LIKE '201974000') WHERE event='Event Name' AS lineitems";

The error that I got on this last one in phpMyAdmin was:

SQL query:

SELECT *(events) FROM (SELECT * AS events FROM calendar WHERE event_key LIKE '201974000') AS lineitems LIMIT 0, 25

MySQL said:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(events) FROM (SELECT * AS events FROM calendar WHERE event_key LIKE '201974000'' at line 1

What I am trying to do is query my database like so:

$sql = "SELECT * FROM calendar WHERE event_key LIKE '".$eventKey."%'";

...and then query those results like so:

$sql = "SELECT * FROM <<RESULTS>> WHERE event='".$eventTitle."'";

Where do I have it wrong?

NOTE: I also tried:

$sql = "SELECT * FROM calendar WHERE (event_key LIKE '".$eventKey."%' AND event='".$event."'";

HERE IS THE COMPLETE PHP FILE:

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>

<body>
    <?php

    function imageCorrection($string){
        $find = array("file/d/","/view?usp=drive_web");
        $replace = array("uc?id=","");
        $string = str_replace($find,$replace,$string);
        return $string;
    }

    function seoUrl($string){
        $src = 'àáâãäçèéêëìíîïñòóôõöøùúûüýÿßÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝ';
    $rep = 'aaaaaceeeeiiiinoooooouuuuyysAAAAACEEEEIIIINOOOOOOUUUUY';
        $find = array("the","and");
        $replace = array("");
        $string = strtr(utf8_decode($string),utf8_decode($src),$rep);
        $string = strtolower($string);
        $string = str_replace("");
        $string = str_replace($find,$replace,$string);
        $string = preg_replace("/[^a-z0-9\s._-]/","",$string);
        $string = preg_replace("/[\s._-]+/"," ",$string);
        $string = preg_replace("/[\s]/","-",$string);
        $string = "/events/".$string;
        if($string = "/events/"){
            $string = null;
        }
        return $string;
    }

    $year = date('Y');
    $lastYear = date('Y',strtotime("last year"));
    $nextYear = date('Y',strtotime("next year"));
    date_default_timezone_set('America/Anchorage');
    $eventTimeZone = new DateTimeZone("America/Anchorage");
    $currentTime = new DateTime("now",$eventTimeZone);
    $eventTimeOffset = $eventTimeZone->getOffset($currentTime);
    use Sabre\VObject;
    include '../../vendor/autoload.php';

    $vcalendar = VObject\Reader::read(fopen('https://calendar.google.com/calendar/ical/i8j5d94tpgnnqu6h8q3mt6uc48%40group.calendar.google.com/public/basic.ics','r'));
    $vcalendar = $vcalendar->expand(new DateTime('2018-01-01'), new DateTime('2020-12-31'));

    $servername = "localhost";
    $username    = "#########";
    $password    = "#########";
    $dbname  = "#########";

    // Create connection
    $connection = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($connection->connect_error){
        die("Connection to calendar failed: " . $connection->connect_error);
    }

    foreach($vcalendar->VEVENT as $event){
        $eventTitle = $event->SUMMARY;
        $eventStart = strtotime($event->DTSTART);
        $eventEnd   = strtotime($event->DTEND);
        $eventDesc  = $event->DESCRIPTION;
        $eventLocat = $event->LOCATION;
        $eventImage = imageCorrection($event->ATTACH);
        $eventKey   = date('YzB',$eventStart);
        $eventURL   = seoUrl($eventTitle);
        echo $eventKey."<br>";
        echo $eventTitle."<br>";
        echo $eventURL."<br>";
        echo $eventDesc."<br>";
        echo $eventLocat."<br>";
        echo $eventImage."<br>";
        echo date('m-d-Y h:ia',$eventStart)."<br>";
        echo date('m-d-Y h:ia',$eventEnd)."<br>";


        $sql = "SELECT * FROM calendar WHERE event_key LIKE '".$eventKey."%' AND event='".$eventTitle."'";
        $update = "UPDATE calendar SET url='".$eventURL."',image='".$eventImage."',description='".$eventDesc."',location='".$eventLocat."',start_date='".$eventStart."',end_date='".$eventEnd."',color='FFB819' WHERE (event_key LIKE '".$eventKey."%' AND event='".$eventTitle."')";
        $insert = "INSERT INTO calendar VALUES ('".$eventNum."', '".$eventTitle."', '".$eventURL."', '".$eventImage."', '".$eventDesc."', '".$eventLocat."', '".$eventStart."', '".$eventEnd."', 'FFB819')";

        $query = $connection->query($sql);
        echo "Query Results: ".$query->num_rows."  EventKey: ".$eventKey."  ".$eventTitle."<br>";
        if($query->num_rows > 0){
            echo "Rows: ==".$query->num_rows."<br>";
            $updateQUERY = $connection->query($update);
        }else{
            echo "Rows: #".$query->num_rows."<br>";
            $key = "SELECT * FROM calendar WHERE event_key LIKE '".$eventKey."%'";
            $query2 = $connection->query($key);
            if($query2->num_rows > 0){
                $eventNum = $query2->num_rows;
                $eventNum++;
                echo "Query 2 Rows: ##".$eventNum."<br>";
            }else{
                $eventNum = 0;
                echo "Query 2 Rows: #".$eventNum."<br>";
            }
            if($eventNum < 10){
                $eventNum = "00".$eventNum;
            }elseif($eventNum < 100){
                $eventNum = "0".$eventNum;
            }
            $eventNum = $eventKey.$eventNum;
            echo "Event#: ".$eventNum."<br>";
            $insertQUERY = $connection->query($insert);
        }
        echo "==============================================================================<br>";
    }


    ?>
</body>
</html>

All of the echo lines are there for testing purposes only to follow the execution of the code.

Please see about sql injection and the importance of prepared and bound queries — Strawberry
– Strawberry, Commented Mar 9, 2019 at 8:30
Incidentally, filtering arrays client side is straightforward process.Here's an example that I particularly like... metafizzy.co — Strawberry
– Strawberry, Commented Mar 9, 2019 at 11:36

Tofunmi · Accepted Answer · 2019-03-09 05:05:09Z

1

I think it is redundant running a sub-query in this instance. Since you want to simply find where event = '$eventTitle' on the result of your first query only, it is just as saying:

$sql = "SELECT * FROM calendar WHERE `event_key` LIKE '%$eventKey%' AND `event` = '$eventTitle'";

NB: notice I didn't use the dot concatenation; because the double quotes surrounding the whole query allows PHP variables within.

Your last trial was close to being right, but you didn't need to open a bracket and even if you wanted to use it, you didn't close it.

answered Mar 9, 2019 at 5:05

Tofunmi

1311 silver badge4 bronze badges

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

7 Comments

PrismaPixel Studios Over a year ago

That doesn't seem to be correct. When I remove the periods and run it in the browser it gives me >> This page isn’t working #### is currently unable to handle this request. HTTP ERROR 500

Tofunmi Over a year ago

Please share the revised query you're trying to run. However, the 'double quote' advantage has been a standing one for a very long time in PHP..

PrismaPixel Studios Over a year ago

I am going to share the entire php document above. When I paste the line of code (minus the $sql =) into phpMyAdmin and replace the variables with actual text, the code executes perfectly. When I run it by accessing the php file on the web, it doesn't query the database properly.

Tofunmi Over a year ago

I think you should paste it. The problem might be on another line.

Tofunmi Over a year ago

Oh... Okay, great! But I think for proper error_reporting and avoid having headaches debugging in a blind environment, turn on your 'display_errors' in your php.ini file. And also, you might wanna use 'try and catch' ... Above all, glad you found the error. And the double-quote without periods is a correct approach too.

|

Collectives™ on Stack Overflow

MySQL query from query in PHP

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related