?
Solved

php

Posted on 2012-03-16
10
Medium Priority
?
271 Views
Last Modified: 2012-03-18
I am trying to ouput a query from database with this code and I probably have a syntax error.
Code works when I put the actual date manually inside the query (eg. <= '$2007-01-01' AND endperiod >= '2007-01-01'");) but when use the variables that I get thru form it doesn't work.

Those same variables work for some other query in that set though.

I would really appreaciate the help.

$start = $_POST["start"];
$end = $_POST["end"];    
        
$start_ts = strtotime($start);
$end_ts = strtotime($end);

$sql=mysql_query("SELECT endperiod from obresti WHERE startperiod <= '$start_ts' AND endperiod >= '$start_ts'");
$row=mysql_fetch_array($sql);
    
$date=$row['endperiod'];
   
echo $date;

Open in new window

0
Comment
Question by:andreaskg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37728679
WHERE startperiod <= '$start_ts' AND endperiod >= '$start_ts'
Where is the test data for this query?
0
 
LVL 10

Expert Comment

by:Tobias
ID: 37728680
Dear,

What you mean by syntax error ? Does you have a error message from php ?

If you get no result, maybe the error is there :

AND endperiod >= '$start_ts'")

to :

AND endperiod >= '$end_ts'")

Regards
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37728690
Here are some of the things you need to consider when using queries and external data.  Please read the code example over and see if it helps you find out how to get some diagnostic information from PHP and MySQL.  MySQL is not a black box.  It can and will fail for reasons that are outside of your control, and your programming must account for this.  You must also filter the external input, and escape it properly before it is used in a query.

A good design pattern for queries is to construct the query in a variable and then pass the variable to mysql_query().  If your script does that, it is easy to visualize the query.  But if the script constructs the query string inside the function call, you cannot see what the fully resolved query string contained.  That is an unnecessary handicap that will hinder your debugging process.
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

Please consider using DATETIME definitions for your columns that deal with date and time information.  It will be easier that way, I promise!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

HTH, ~Ray
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:andreaskg
ID: 37728692
Data is like this, and actual table is in attachment.


id    startperiod    endperiod    daysperiod    interest    daysyear
1    2004-07-01    2004-12-31    184    0.1550    366
2    2005-01-01    2005-12-31    365    0.1550    365
3    2006-01-01    2006-06-30    181    0.1350    365
4    2006-07-01    2006-12-31    184    0.1250    365
5    2007-01-01    2007-06-30    181    0.1150    365
6    2007-07-01    2007-12-31    184    0.1200    365
7    2008-01-01    2008-12-31    366    0.1200    366
8    2009-01-01    2009-06-30    181    0.1050    365
9    2009-07-01    2009-12-31    184    0.0900    365
10    2010-01-01    2010-12-31    365    0.0900    365
11    2011-01-01    2011-06-30    181    0.0900    365
12    2011-07-01    2011-12-31    184    0.0925    365
13    2012-01-01    2012-06-30    182    0.0900    366
obresti1.sql
0
 

Author Comment

by:andreaskg
ID: 37728698
To Madshiva:

Well the code as it is actually doesn't output date.  But if I input date like 2007-01-01 instead of $start_ts it gives out the correct answer.

It should be same date both times.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37728728
Are startperiod and endperiod columns defined as DATETIME?
0
 
LVL 10

Accepted Solution

by:
Tobias earned 1000 total points
ID: 37728730
strtotime convert to unixtime, then it's normal that it return nothing.

remove and comment the two line  " strtotime(...);" and it will work.

Regards
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37728742
Ahh, just saw the SQL definitions.  These are DATE columns.  Here is a general way to handle a comparison.  BETWEEN is inclusive.  Not sure this is exactly what you are after and it is obviously untested code, but it's probably a place to start.
$alpha = date('c', strtotime($_POST["start"]));
$omega = date('c', strtotime($_POST['end']));
$sql = "SELECT thing FROM table WHERE ( '$alpha' BETWEEN startperiod AND endperiod ) AND ( '$omega' BETWEEN startperiod AND endperiod ) ";

Open in new window

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 37728748
Now having said that, you need to be aware that PHP strtotime() will return FALSE when the input date string is unusable.  If you feed FALSE to the PHP date() function you will get something close to January 1, 1970.  So it is a good idea to test the output from strtotime() to be sure the function actually returned a UNIX timestamp!
0
 

Author Closing Comment

by:andreaskg
ID: 37734306
I've made it work.  The problem was that I didn't include AS parameter, and that way I didn't know how to call the result.

Also the problem was with date. And I would like to thank  ray and madshiva to make me check that part of the code. I was sure that was correct way.


The code is like this:


$start = $_POST["start"];
$end = $_POST["end"];
$sqlid2=mysql_query("SELECT id AS ident2 from obresti WHERE startperiod <= '$end' AND endperiod >= '$end'");
$rowid2=mysql_fetch_array($sqlid2);
	
 $ident2=$rowid2['ident2'];
 echo $ident2;

Open in new window

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question