Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 19th, 2014, 02:29 AM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2014
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default php search a mysql table by date...no ranges, no times

I have a great deal of respect for what you all do. I used to be a stock analyst & earned an income. For over a year I have aged faster than the president of the U.S. trying to grasp excel, php, html, mysql languages and coding. No sooner do I start to grasp one when it becomes 'deprecated.' I have been hacked a dozen times and damn near killed over this.

I developed a very reliable system of volume analysis. I created new data methods and in some cases had to create a datasource of existing information, just because while they say they want it public, they make it nearly impossible to get.

I just want to change the world before I die by giving investors transparency into the market and level the field for everyone. I've done it. I have a patent applied for on it. I even managed to create the world's first searchable database of short sale volume and dark pool volume. My problem is getting it user friendly.

Eventually I want pretty pictures in the form of charts. People understand pictures better than raw data in a table.

What I am killing myself over the past week, and am begging for help with, seems to me to be so simple, I am ashamed to ask for help with it. I have to though before I lose what little I have left...

I can query mysql table ithout issue, and the result set comes back as expected. phpmyadmin will even convert the working mysql query into php code..

The problem is, the given code returns no results...I think it's a date issue given the date time functions built in to php and mysql. I can find a million topics on searching between times, and searching date in datetime formats. Everything I've read, seen, tried, rewrote, renamed etc, has failed.

I checked the mysql table for date type: It comes back as "date". that means a format of yyyy-mm-dd.

The data was loaded as yyyymmdd (20140317) but the tables indicate the dashes are used. However, a query with dashes returns no results..Only without the dashes do I get the query to return a result set.

I have tried to run the search using an html form and php code 1000 ways...changing formats, etc...no results are returned with or without dashes, slashes, etc...

I'm thinking the variable used in the query needs to be formatted as a date..however, I cannot find a way to do this either. perhaps I've just went past my breaking point, but I am at a loss here..

After all that, consider that this returns the desired results in mysql:

PHP Code:
$sql "SELECT * FROM `table` WHERE `date` = "20140317" and `green_flag_alert` = "Green Flag Alert"; 

yet when I use the statement with a variable, I get no results, nor errors.


PHP Code:
$sql "SELECT * FROM `table` WHERE `date` = [b]"$query"[/b] and `green_flag_alert` = "Green Flag Alert"; 
(For testing purposes I removed the green flag alert aspect just to confirm the problem as the date variable)

So here is a combination of attempts with some items // out for test purposes again. You can run it and two other searches at the following url:

http://satwaves.com/short-volume-dar...volume-search/

You'll notice the first search works if you enter a ticker symbol.

The next two refuse to work (red and green flag alerts) which are nearly identical in code --w/the exceptions of the html form input types, php variables tweaked slightly and mysql query statement.

NOT working

HTML Code:
<h2>Green Flag Alerts:</h2>	
  <table id="Green Flag Alerts">
  <table border="1">
 <tr>
  <th>ID</th>
  <th>Date</th>
  <th>Symb</th>
  <th>Open</th>
  <th>High</th>
  <th>Low</th>
  <th>Close</th>
  <th>$ Ch.</th>
  <th>% Ch.</th>
  <th>Ttl Vol</th>
  <th>Avg Vol</th>
  <th>Short Vol</th>
  <th>Open Vol</th>
  <th>DP Vol</th>
  <th>Open Sht %</th>
  <th>Ttl Sht % </th>
  <th>DP Open % </th>
  <th>DP Ttl % </th>
  <th>Alert</th>
  <th>Alert</th>
 
  </tr>
PHP Code:
<?php
include("conn.php");
//$gfquery = $_GET['GF_query'];
//$gfquery = str_replace("-", "", $gfquery);
//$gfquery = date(Y-m-d); 
//$gfquery = strtotime($_GET["date"]));
//$gfquery = htmlspecialchars($gfquery);
//$gfquery = mysql_real_escape_string($gfquery);
$sql 'SELECT * FROM `dataset` WHERE `date` = "$gfquery" AND `green_flag_alert` = "Green Flag Alert"';

$result=mysql_query($sql);
    if (!
$result) {
      echo 
'Could not run query: ' mysql_error();
     exit;
     }

    while (
$row mysql_fetch_array($result))  {
    
?>
Code:
 <tr>
        <td><?php echo $row['id'];?></td>
   	<td><?php echo $row['date'];?></td>
        <td><?php echo $row['symbol'];?></td>
   	<td><?php echo $row['open'];?></td>
   	<td><?php echo $row['high'];?></td>
	<td><?php echo $row['low'];?></td>
   	<td><?php echo $row['close'];?></td>
   	<td><?php echo $row['price_change'];?></td>
   	<td><?php echo $row['percent_change'];?></td>
	<td><?php echo $row['total_volume'];?></td>
	<td><?php echo $row['avg_volume'];?></td>
	<td><?php echo $row['short_volume'];?></td>
   	<td><?php echo $row['open_volume'];?></td>
   	<td><?php echo $row['dark_pool_volume'];?></td>
	<td><?php echo $row['open_short_percent'];?></td>
   	<td><?php echo $row['total_short_percent'];?></td>
   	<td><?php echo $row['dark_pool_open_percent'];?></td>
        <td><?php echo $row['dark_pool_total_percent'];?></td>
        <td><?php echo $row['green_flag_alert'];?></td>
	<td><?php echo $row['red_flag_alert'];?></td>
   </tr>

<?php 
}
?>	
</table>
WORKING :

HTML Code:
<h2>Results:</h2>	
  <table id="Total Short & Dark Pool Volume Search Results">
  <table border="1">
  <tr>
  <th>ID</th>
  <th>Date</th>
  <th>Symb</th>
  <th>Open</th>
  <th>High</th>
  <th>Low</th>
  <th>Close</th>
  <th>$ Change</th>
  <th>% Change</th>
  <th>Ttl Vol</th>
  <th>Avg Vol</th>
  <th>Short Vol</th>
  <th>Open Vol</th>
  <th>DP Vol</th>
  <th>Open Short %</th>
  <th>Ttl Short % </th>
  <th>DP Open % </th>
  <th>DP Ttl % </th>
  <th>Alert</th>
  <th>Alert</th>
 
  </tr>
PHP Code:
<?php
include("conn.php");
$query $_GET['query'];
$query strtoupper($query);
$query htmlspecialchars($query);
$query mysql_real_escape_string($query);
include(
"createtable.php"); 
   
//I added the above to create a table  for every stock 
//searched for by the user, which becomes a txt file on my server and a csv //download for use with charts later.

$sql "SELECT DISTINCT * FROM `dataset` WHERE `symbol` = '$query' ORDER BY Date DESC";

$result=mysql_query($sql);
    if (!
$result) {
      echo 
'Could not run query: ' mysql_error();
     exit;
     }

    while (
$row mysql_fetch_array($result))  {
    
?>
Code:
 <tr>
        <td><?php echo $row['id'];?></td>
   	<td><?php echo $row['date'];?></td>
        <td><?php echo $row['symbol'];?></td>
   	<td><?php echo $row['open'];?></td>
   	<td><?php echo $row['high'];?></td>
	<td><?php echo $row['low'];?></td>
   	<td><?php echo $row['close'];?></td>
   	<td><?php echo $row['price_change'];?></td>
   	<td><?php echo $row['percent_change'];?></td>
	<td><?php echo $row['total_volume'];?></td>
	<td><?php echo $row['avg_volume'];?></td>
	<td><?php echo $row['short_volume'];?></td>
   	<td><?php echo $row['open_volume'];?></td>
   	<td><?php echo $row['dark_pool_volume'];?></td>
	<td><?php echo $row['open_short_percent'];?></td>
   	<td><?php echo $row['total_short_percent'];?></td>
   	<td><?php echo $row['dark_pool_open_percent'];?></td>
        <td><?php echo $row['dark_pool_total_percent'];?></td>
        <td><?php echo $row['green_flag_alert'];?></td>
	<td><?php echo $row['red_flag_alert'];?></td>
   </tr>
PHP Code:
<?php 
}
?>
HTML Code:
</table>
Thank you in ADVANCE..I have to update tomorrows data now...It'll be 5 am here when I'm done... its 1:30 now..I cant wait to hear what i missed in all of it...
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please Help in search query using php & mysql farwall.ghost Beginning PHP 0 January 29th, 2014 04:38 PM
search in PHP/MySQL lanc71 MySQL 0 March 4th, 2006 08:10 PM
date ranges yuqlin BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 December 23rd, 2004 05:23 PM
How to script search form in PHP/MYSQL javabeans PHP How-To 0 January 12th, 2004 03:53 AM
date mysql to php display Geeman PHP Databases 4 September 1st, 2003 09:13 AM



All times are GMT -4. The time now is 01:07 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.