1

So I'm trying to get records from database by using this code, but I'm only getting else alert message. Can somebody say what I am doing wrong or where is an error in the code? It populates the dropdown, but like I said I don't get the result only the alert message:

<form  method='post' action='grafikastest.php'> 
    <select id="name">
        <?php  
        include_once ('inc\connect.php'); 
        echo "Pasirinkite datą: &nbsp";  

        $date = strtotime("+0 day");
        $dtt=date('Y-m-d', $date); 
        echo "<option value=$dtt>$dtt</option>";

        $date = strtotime("+1 day"); 
        $dtt=date('Y-m-d', $date);
        echo"<option value=$dtt>$dtt</option>"; 

        $date = strtotime("+2 day"); 
        $dtt=date('Y-m-d', $date);
        echo "<option value=$dtt>$dtt</option>";   

        $date = strtotime("+3 day"); 
        $dtt=date('Y-m-d', $date);
        echo "<option value=$dtt>$dtt</option>";  

        $date = strtotime("+4 day"); 
        $dtt=date('Y-m-d', $date);
        echo "<option value=$dtt>$dtt</option>"; 

        $date = strtotime("+5 day"); 
        $dtt=date('Y-m-d', $date);
        echo "<option value=$dtt>$dtt</option>";   

        $date = strtotime("+6 day"); 
        $dtt=date('Y-m-d', $date);
        echo "<option value=$dtt>$dtt</option>";

        $sql = "SELECT  ID, data, laikas, laikas2 FROM vizitai4 WHERE darb_stat NOT LIKE 'Atlikta' and data LIKE '%" . $dtt .  "%'  OR
darb_stat IS NULL and data LIKE '%" . $dtt .  "%' GROUP BY laikas";

        $result = mysql_query($sql); 
        //rodymas lenteleje
        echo $dtt;
        echo "<table id=t01>
                <tr>
                    <th>Data</th>
                    <th>Remonto pradžia</th>
                    <th>Remonto pabaiga</th>
                </tr>";

        if(mysql_num_rows($result) > 0) {
            while( $row=mysql_fetch_array($result)) { 
                echo "<tr>";
                echo "<td>" . $row['data'] . "</td>";
                echo "<td>Remontas prasideda nuo " . $row['laikas'] .  " </td>";
                echo "<td>Numatomas remonto baigimo laikas " . $row['laikas2'] .  " </td>";
                echo "<td style='display: none;'><form method=post>
                    <input name=id type=hidden value='".$row['id']."';>

                    </form></td>";
                echo "</tr>";
            }
        }else{
            echo "<script>alert('Šios dienos įrašų nėra');</script>";
        }

        echo "</table>"; 
        ?> 
    </select>
    <input  type="submit" name="submit" value="Ieskoti"> 
</form>  
</body> 
</html>    
Rasclatt
  • 12,498
  • 3
  • 25
  • 33
Mantas
  • 25
  • 1
  • 1
  • 6
  • 1
    Why do you have a table in ` – Rasclatt Jun 01 '16 at 03:59
  • to echo the data into a table – Mantas Jun 01 '16 at 04:01
  • So this is all on one page, this is actually what the script looks like? – Rasclatt Jun 01 '16 at 04:05
  • A few things, if this is what the script actually looks like: 1) you should move the table out of the ` – Rasclatt Jun 01 '16 at 04:14
  • Lastly, have you manually tried to run the sql statement? That is to say, instead of writing a dynamically created sql statement, add hardcoded values to see if even doing it that way pulls the data you want? – Rasclatt Jun 01 '16 at 04:16
  • yes the query works, it returns me the result I want. – Mantas Jun 01 '16 at 04:20
  • So if you have the `$dtt` in there it doesn't work? – Rasclatt Jun 01 '16 at 04:21
  • I mean I tried the code in my local server on phpmyadmin tool, it the query works – Mantas Jun 01 '16 at 04:37
  • if you `echo $sql;` does the sql statement look normal? – Rasclatt Jun 01 '16 at 04:39
  • SELECT ID, data, laikas, laikas2 FROM vizitai4 WHERE darb_stat NOT LIKE 'Atlikta' and data LIKE '%2016-06-07%' OR darb_stat IS NULL and data LIKE '%2016-06-07%' GROUP BY laikas – Mantas Jun 01 '16 at 04:44
  • But if you just write `echo` before `$sql = "SELECT ID, data, laikas, laikas2 FROM vizitai4 WHERE darb_stat NOT LIKE 'Atlikta' and data LIKE '%" . $dtt . "%' OR darb_stat IS NULL and data LIKE '%" . $dtt . "%' GROUP BY laikas";` it should write the line so you can see if the `$dtt` in the query – Rasclatt Jun 01 '16 at 04:45
  • it always takes the last one from the – Mantas Jun 01 '16 at 04:46
  • Ah, now I know what you are saying. PHP runs all in one shot, meaning you can not interact after PHP runs. If you want to have the page do stuff after page loads, you need javascript, or you need to submit the form' – Rasclatt Jun 01 '16 at 04:49
  • 1
    yeah but why it takes always the last value from select menu? it ignores what ever I take else. How do I do the submit form and make this work? Can you help me? – Mantas Jun 01 '16 at 04:54
  • It take the last value because you set it in the last option of the select menu. After that, the PHP is done. If you want the dropdown to work when you select after the page loads, you need javascript. – Rasclatt Jun 01 '16 at 04:56
  • how do I accomplish such thing? – Mantas Jun 01 '16 at 05:12
  • You will want to use ajax. I would suggest jQuery, it's easy and pretty straight forward. – Rasclatt Jun 01 '16 at 05:13
  • 1
    I barely know these things is there anyway u could provide some code according to my issue or given example? – Mantas Jun 01 '16 at 05:23

1 Answers1

1

From our exchange, the core of the issue is that you want interaction with the form to change the form itself and that you are trying to accomplish this with only PHP. Since PHP runs once to generate the page, you can not interact with the form elements after and have PHP react to those changes. You need a triggering effect that will re-query PHP. This is a job for a client-side language such as JavaScript.

Since you asked for an example here is a basic one. It is no easy task and has a bunch of moving parts, so I have a feeling that if you "barely know these things..." with what you have now, you will really be in the dark with this answer.

Follow the instruction and it should work for you. I have tested most of this (or have copied snippets from scripts I use) so pay attention to what things are called and where they are placed and read the notation. I suggest you create this directory/file structure as I have it, then test it out there before you attempt to do any modification to your working document(s). Once you do this stuff, if you don't know what you are doing it's near impossible to troubleshoot. Also take note I can not, in good conscience, answer this question without removing the reference to mysql_, those functions are deprecated or removed completely (php7):

/config.php

<?php
// This page goes on every main page of your site
// Comment out the error lines when live,
// you don't want errors showing on your page except in test
session_start();
ini_set("display_errors",1);
error_reporting(E_ALL);
define('DB_HOST','yourdbhost');
define('DB_USERNAME','yourdbusername');
define('DB_PASSWORD','yourdbpass');
define('DB_NAME','yourdbname');
define('SITE_ROOT',__DIR__);
define('DS', DIRECTORY_SEPARATOR);
define('CLASS_DIR', SITE_ROOT.DS.'core'.DS.'classes');
define('FUNCTION_DIR', SITE_ROOT.DS.'core'.DS.'functions');
require_once(FUNCTION_DIR.DS.'autoloader.php');
// This will help to autoload your classes
// **RESOURCE:** http://php.net/manual/en/function.spl-autoload-register.php
spl_autoload_register('autoloader');
// put whatever else you want on this page to make available to your site.
// You don't need the database connection however

/core/functions/makeOptions.php

<?php
/*
** @description This function uses a for loop to make your options
**              You need to think how to use php to make your script(s)
**              for you
**              **RESOURCE:** http://php.net/manual/en/control-structures.for.php
** @param $max [int] This is how many options the function will make
*/
function makeOptions($max = 6)
    {
        for($i = 0; $i <= $max; $i++) {
            $date   =   strtotime("+".$i." day");
            $dtt    =   date('Y-m-d', $date);
            echo '<option value="'.$dtt.'">'.$dtt.'</option>'.PHP_EOL;
        }
    }

/core/functions/autoloader.php

<?php
/*
** @description This function is used to autoload classes
** @param $class [string] This is automated and is populated by spl_autoload_register()
** **RESOURCE:** http://php.net/manual/en/function.spl-autoload-register.php
*/
function autoloader($class)
    {
        if(class_exists($class))
            return true;

        if(is_file($inc = CLASS_DIR.DS.$class.".php"))
            include_once($inc);
    }

/page1.php

<?php
// Include our config file
require_once(__DIR__.DIRECTORY_SEPARATOR.'config.php');
// Include the function to create our options
include_once(FUNCTION_DIR.DS.'makeOptions.php');
?><!DOCTYPE html>
<html>
<head>
<!-- include in head with anything else you need -->
<!-- **RESOURCE:** http://www.jquery.com/ -->
<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script type="text/javascript" src="http://code.jquery.com/ui/1.9.2/jquery-ui.js"></script>
<script>
$(document).ready(function(){
    // Observe any change made to select with name=name
    $(this).on('change','select[name=name]',function() {
        // get the value of that selection
        var thisVal =   $(this).val();
        // Use ajax to query page2.php
        // **RESOURCE:** http://api.jquery.com/jquery.ajax/
        $.ajax({
            // This is the link that will load the contents of the form
            url: '/page2.php',
            data: {
                // This is equivalent to $_POST['name']
                "name":thisVal
            },
            type: 'POST',
            success: function(response) {
                // On success of the ajax, this will post the contents of
                // page2.php into the div with id=loadspot
                $("#loadspot").html(response);
            }
        });
    });
});
</script>
</head>
<body>
<form  method='post' action='grafikastest.php'>
    <!--
        I am not sure the placement of this text, but it can not be
        in the middle of a drop down
    -->
    Pasirinkite datą: &nbsp
    <select id="name" name="name">
        <option value="">Select</option>
        <!-- Apply the options function -->
        <?php makeOptions(); ?> 
    </select>
    <!-- This is where you will load the contents of the dropdown via ajax -->
    <div id="loadspot"></div>
    <input  type="submit" name="submit" value="Ieskoti"> 
</form>
</body>
</html>

/core/classes/Database.php

<?php
/*
** @description This class is your new database which replaces your `mysql_`
** **RESOURCE:** http://php.net/manual/en/pdo.connections.php
*/
class Database
    {
        // static elements are kind of like a global
        private static  $singleton;
        private static  $con;
        // This will save the class for reuse
        public  function __construct()
            {
                if(self::$singleton instanceof Database)
                    return self::$singleton;

                self::$singleton    =   $this;
            }
        // This is the connection to the database
        public  function connect()
            {
                if(self::$con instanceof PDO)
                    return self::$con;
                // Review the PDO class for instruction how to make this
                // connection better using some PDO presets (Emulation of prepares, etc)
                // **RESOURCE** http://php.net/manual/en/pdo.constants.php
                self::$con  =   new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USERNAME,DB_PASSWORD);

                return self::$con;
            }
    }

/core/classes/qEngine.php

<?php
/*
** @description This class is what you use to safely query your database
** **RESOURCE:** http://php.net/manual/en/pdo.prepare.php
** **RESOURCE:** http://php.net/manual/en/pdo.query.php
** **RESOURCE:** http://php.net/manual/en/pdostatement.fetch.php
** 
*/
class qEngine
    {
        private static  $con;
        private $query;

        public  function query($sql = false,$bind = false)
            {
                // This checks if the connection is already saved
                // You can also use "dependency injection" to pass your
                // database connection to this class. I prefer to use
                // a static connection but here is how that dependency works:
                // **RESOURCE** http://code.tutsplus.com/tutorials/dependency-injection-in-php--net-28146
                // **RESOURCE** https://en.wikipedia.org/wiki/Dependency_injection
                if(!(self::$con instanceof PDO)) {
                    // Makes connection
                    $dbEngine   =   new \Database();
                    self::$con  =   $dbEngine->connect();
                }
                // Creates a bind array
                // **RESOURCE** http://php.net/manual/en/pdostatement.bindvalue.php
                // **RESOURCE** http://php.net/manual/en/pdostatement.execute.php (Example #2)
                // **RESOURCE** http://php.net/manual/en/pdostatement.bindparam.php
                if(!empty($bind)) {
                    $bArray =   false;
                    foreach($bind as $key => $value) {
                        $bKey           =   ":{$key}";
                        $bArray[$bKey]  =   $value;
                    }
                }
                // If there is a bind array, it will run a prepare
                if(!empty($bArray)) {
                    $this->query    =   self::$con->prepare($sql);
                    $this->query->execute($bArray);
                }
                // If no bind, it will run a straight query
                else {
                    $this->query    =   self::$con->query($sql);
                }
                // This returns the object for method chaining
                // **RESOURCE** http://stackoverflow.com/questions/3724112/php-method-chaining
                return $this;
            }

        public  function getResults()
            {
                // This will check that a query has been made
                if(empty($this->query))
                    return 0;
                // This will loop through the results and save to 1D array
                while($result = $this->query->fetch(PDO::FETCH_ASSOC))
                    $row[]  =   $result;
                // This will return either the array or a "0"
                return (!empty($row))? $row : 0;
            }
    }

/core/functions/getListByDate.php

<?php
/*
** @description This function will query your database safely
** @param $date [string] Receives a date by string
*/
function getListByDate($date)
    {
        $bind   =   array('%'.$date.'%','%'.$date.'%');
        return (new \qEngine()) ->query("SELECT `ID`,`data`,`laikas`,`laikas2`
                                        FROM `vizitai4`
                                        WHERE `darb_stat`
                                        NOT LIKE 'Atlikta'
                                        AND `data`
                                        LIKE :0
                                        OR `darb_stat`
                                        IS NULL AND `data`
                                        LIKE :1
                                        GROUP BY `laikas`",$bind)
                                ->getResults();
    }

/page2.php

<?php
// Include our config file
require_once(__DIR__.DIRECTORY_SEPARATOR.'config.php');
// Include the function to query the database
include_once(FUNCTION_DIR.DS.'getListByDate.php');
// This is the page that the AJAX queries from page1.php

// See if the post has been made
if(!empty($_POST['name'])) {
    // Fetch data from database
    $vizitai4   =   getListByDate($_POST['name']);
?>
<table id="t01">
    <tr>
        <th>Data</th>
        <th>Remonto pradžia</th>
        <th>Remonto pabaiga</th>
    </tr>
<?php
    if($vizitai4 != 0) {
        foreach($vizitia4 as $row) { 
?>  <tr>
        <td><?php echo $row['data']; ?></td>
        <td>Remontas prasideda nuo <?php echo $row['laikas']; ?></td>
        <td>Numatomas remonto baigimo laikas <?php echo $row['laikas2']; ?> </td>
        <td style="display: none;"><input name="id[]" type="hidden" value="<?php echo $row['id']; ?>" /></td>
    </tr>
<?php
        }
    }
    else {
?>
<script>
    alert('Šios dienos įrašų nėra');
</script>
<?php
    }
?>
</table>                
<?php
}

Here is the basic construct of what is happening:

enter image description here

Rasclatt
  • 12,498
  • 3
  • 25
  • 33