displaying sql table using order by

kingb00zer · June 20, 2016

Hi I cant seem to get my data to display correctly, at the moment it is ordered correctly but repeats the same data continuously instead of the entire table. I used to know how to do this but have not been actively coding for a couple of years now. Can someone please point me in the right direction? thanks.

// select all data from tracker table and order from by highest movement to lowest movement

$sql = "SELECT * FROM `tracker` ORDER BY movement ASC";

$qry= mysql_query($sql);

$result= mysql_num_rows($qry);

$count2= 1;

while ($count2 <= $result )

{

while ($row= mysql_fetch_array($qry))

{

$code= $row['code'];

$current= $row['25'];

$up= $row['ups'];

$down= $row['downs'];

$evens= $row['evens'];

$movement= $row['movement'];

$bigup= $row['bigup'];

$bigdown= $row['bigdown'];

$upcount= $row['upcount'];

$downcount= $row['downcount'];

$twentyfive= $row['25'];

$twentyfour= $row['24'];

$commission= 15; // edit this to be number of shares worht 20000 divided by .05

}

// her is where the code that would display the details goes, there is no errors here

$count2++;

}

I removed most of the code that I know works and left the part where Im positive im wrong.

Edited June 20, 2016 by KingB00ZeR

Ingolme · June 20, 2016

If you're not using those variables inside the loop in which they're set then you will only see the values of the last row.

What exactly are you trying to do with $count2 and result?

kingb00zer · June 21, 2016

$count2 and $result im attempting to use to trigger the loop to end but im almost certain that im using the wrong kind of loop to display this information. i wish my external hd wasnt a 10 hour drive away, I did this a few years ago on a previous project but forget what i did to make it work lol

I managed to make the loop stop when it is supposed to by making this change pasted below but I am a little unsure how to select the correct row from the database in the loop. Also im lacking a where clause because im not what what to put after WHERE..

// select all data from tracker table and order from by highest movement to lowest movement

$sql = "SELECT * FROM `tracker` ORDER BY movement ASC";

$qry= mysql_query($sql);

$result= mysql_num_rows($qry);

for($count2 = 0; $count2 <= $result; $count2++ )

{

$sql = "SELECT * FROM `tracker` ORDER BY movement ASC";

$qry= mysql_query($sql);

while ($row= mysql_fetch_array($qry))

{

$code= $row['code'];

$current= $row['25'];

$up= $row['ups'];

$down= $row['downs'];

$evens= $row['evens'];

$movement= $row['movement'];

$bigup= $row['bigup'];

$bigdown= $row['bigdown'];

$upcount= $row['upcount'];

$downcount= $row['downcount'];

$twentyfive= $row['25'];

$twentyfour= $row['24'];

$commission= 15; // edit this to be number of shares worht 20000 divided by .05

}

// here is where the code that would display the details goes, there is no errors here

}

EDIT: I also think I might be needing to use the foreach loop but could not remember how to do so correctly to save my life lol

Edited June 21, 2016 by KingB00ZeR

Ingolme · June 21, 2016

This alone will work, the outer loop is completely unnecessary.

$sql = "SELECT * FROM `tracker` ORDER BY movement ASC";
$qry= mysql_query($sql);

while ($row= mysql_fetch_array($qry)) {
  $code= $row['code'];
  $current= $row['25'];
  $up= $row['ups'];
  $down= $row['downs'];
  $evens= $row['evens'];
  $movement= $row['movement'];
  $bigup= $row['bigup'];
  $bigdown= $row['bigdown'];
  $upcount= $row['upcount'];
  $downcount= $row['downcount'];
  $twentyfive= $row['25'];
  $twentyfour= $row['24'];
  $commission= 15;

  // Display information here
}

I would recommend not using the MySQL library, it is deprecated because it is insecure. Use PDO or MySQLi

kingb00zer · June 22, 2016

that works perfectly, i over complicated the way I thought about it with my attempts. thank you so much.

kingb00zer · July 3, 2016

I seem to have encountered another issue with this script which has me baffled. Im going to paste the full script along with the script it calls on which works fine however after calling on the script (autobuy.php) it then gives me an error on the original page after having only displayed 1 row. what I dont understand is that autobuy.php doesnt make any changes to the table in the database which im trying to list. its probably something very simple but i cant get my head around it.

page2.php (this page is set to auto refresh and display a list of 100 stocks in order of movement, this works fine until something is purchased in the autobuy.php script. I get this error Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in C:\xampp\htdocs\page2.php on line 21

<?php

set_time_limit(360);

include('connection.php');

//include('trackingtable.php');

echo "<br />";

/**

* Printing out the data

*/

echo "<html><head><link rel='stylesheet' type='text/css' href='stockstyle.css'></head><body><table border='1'>";

echo "<tr><td class='one'>CODE</td><td class='one'>CURRENT PRICE</td><td class='spacer'></td><td class='track'>UPS</td><td class='track'>DOWNS</td><td class='track'>EVENS</td><td class='track'>MOVEMENT</td><td class='track'>AVG UP</td><td class='track'>AVG DOWN</td><td class='track'>BIGGEST UP</td><td class='track'>BIGGEST DOWN</td><td class='track'>LAST CHANGE</td><td class='one'>COMMISSION</td><td class='one'>CODE</td></tr>";

// set autobuy trip value

//$count= 1;

// select all data from tracker table and order from by highest movement to lowest movement

$sql = "SELECT * FROM `tracker` ORDER BY movement DESC";

$qry= mysql_query($sql);

while ($row= mysql_fetch_array($qry))

{

$code= $row['code'];

$current= $row['25'];

$up= $row['ups'];

$down= $row['downs'];

$evens= $row['evens'];

$movement= $row['movement'];

$bigup= $row['bigup'];

$bigdown= $row['bigdown'];

$upcount= $row['upcount'];

$downcount= $row['downcount'];

$twentyfive= $row['25'];

$twentyfour= $row['24'];

$commission= 15; // edit this to be number of shares worht 20000 divided by .05

include('autosell.php');

// determine if change is up down or even and color code

if($current > 0)

{

$classchange= "up2";

} else if($current < 0)

{

$classchange= "down2";

} else

{

$classchange= "even2";

}

$bigdown2= abs($bigdown);

if ($bigup > $bigdown2)

{

$classup="up3";

}else

{

$classup="track2";

}

$difference= $twentyfive - $twentyfour;

if($difference > 0)

{

$classtype='up3';

} else if($difference < 0)

{

$classtype='down3';

} else

{

$classtype='even3';

}

//$na= "N/A";

//if ($stock[5] == $na)

//{

$current= $twentyfive;

//} else

//{

//$current= $stock[5];

//}

//error_reporting(0);

// get average ups and downs

$avgup = $upcount / $up;

$avgup= number_format($avgup,2);

$avgdown = $downcount / $down;

$avgdown= number_format($avgdown,2);

$avgdown2= abs($avgdown);

if($avgup > $avgdown2)

{

$classavg="up3";

}else

{

$classavg="track2";

}

$downs2= $down * 1.3;

if($up > $downs2)

{

$classmove="up3";

}else

{

$classmove="track2";

}

if ($up >= 8 && $up > $downs2 && $avgup > $avgdown2 && $bigup > $bigdown2)

{

$classbuy= "buy";

include('autobuy.php');

} else

{

$classbuy="three";

}

if ($movement > 0)

{

$classmove2= "up3";

} else

{

$classmove2= "track2";

}

echo "<tr><td class='three'>$code</td></td><td class='two' align='center'>$$current</td><td class='spacer'></td><td class='$classmove'>$up</td><td class='track2'>$down</td><td class='track2'>$evens</td><td class='$classmove2'>$movement %</td><td class='$classavg'>$avgup</td><td class='track2'>$avgdown</td><td class='$classup'>$bigup</td><td class='track2'>$bigdown</td><td class='$classtype'>$difference</td><td class='track2'>$commission</td><td class='$classbuy'>$code</td></tr>";

//$count2++;

}

echo "</table>";

$queryg= "SELECT * FROM userinfo";

$resultg = mysql_query($queryg) or die(mysql_error());

while($row = mysql_fetch_array($resultg))

{

$name= $row['user'];

$money= $row['money'];

}

echo "<table class='userinfo' >";

echo "<tr><td>$name </td><td>Cash: $$money</td></tr>";

echo "</table>";

echo "</body></html>";

?>

autobuy.php (this script is triggered on occasion and theoretically buys shares and stores the information in the database in a completely different table to what im displaying in page2.php)

<?php

include('connection.php');

// check if stock is already purchased

$querybuy3= "SELECT * FROM transactions WHERE code='$code'";

$resultbuy3 = mysql_query($querybuy3) or die(mysql_error());

$check= mysql_num_rows($resultbuy3);

if ($check == 0)

{

//if ($count == 1)

//{

// set the buying parameter

$querybuy= "SELECT * FROM tracker WHERE code='$code'";

$resultbuy = mysql_query($querybuy) or die(mysql_error());

while($row = mysql_fetch_array($resultbuy)){

$bigdown= $row['bigdown'];

$bigup= $row['bigup'];

$price= $row['25'];

}

if ($bigdown == 0)

{

$stop= $price * .035;

} else

{

$stop= $bigdown;

}

$stop= $stop * 1.2;

$stop= abs($stop);

$stop= round($stop,2);

$querybuy2= "SELECT * FROM userinfo WHERE user='ash'";

$resultbuy2 = mysql_query($querybuy2) or die(mysql_error());

while($row = mysql_fetch_array($resultbuy2)){

$money= $row['money'];

//$shares= $row['shares'];

//$networth= $row['totalvalue'];

//$trades= $row['tradecount'];

}

if($money >= 20030)

{

$commission= 15;

$amount= 20000 / $current;

$amount= floor($amount);

$cost= $current * $amount + $commission;

$sellprice= $current - $stop;

if($money > $cost)

{

// make first purchase

mysql_query("INSERT INTO `transactions` (`code`, `cost`, `quantity`, `trailingstop`, `high`, `sold`, `sellprice` ) VALUES ('$code', '$current', '$amount', '$stop', '$current', 'no', '$sellprice' )");

// update user info

$money= $money - $cost;

//$shares= $shares + $cost;

//$networth= $money + $shares;

//$trades++;

mysql_query("UPDATE `userinfo` SET `money`= '$money' WHERE `userinfo`.`user`='ash'");

//mysql_query("UPDATE `userinfo` SET `shares`= '$shares' WHERE `userinfo`.`shares`='ash'");

//mysql_query("UPDATE `userinfo` SET `totalvalue`= '$networth' WHERE `userinfo`.`totalvalue`='ash'");

//mysql_query("UPDATE `userinfo` SET `tradecount`= '$trades' WHERE `userinfo`.`tradecount`='ash'");

echo "you purchased $amount shares of $code for $$cost with a commision of $commission, you have set your trailing stop to $stop <br />";

//$count++;

}

//}

}

?>

Ingolme · July 3, 2016

The error says that the function is getting a string and it requires a resource. Output the value that you're sending to the function to see why it's a string.

Please put your code

In a code block.

Just wrap it in [ code] tags.

kingb00zer · July 3, 2016

it echos out Resource id #5

sorry about that, i forgot about the code block.

Ingolme · July 3, 2016

What is on line 21 and the surrounding lines of page2.php?

kingb00zer · July 3, 2016

<?php
set_time_limit(360);
include('connection.php');
//include('trackingtable.php');
echo "<br />";
/**
 * Printing out the data
 */
 
 echo "<html><head><link rel='stylesheet' type='text/css' href='stockstyle.css'></head><body><table border='1'>";
echo "<tr><td class='one'>CODE</td><td class='one'>CURRENT PRICE</td><td class='spacer'></td><td class='track'>UPS</td><td class='track'>DOWNS</td><td class='track'>EVENS</td><td class='track'>MOVEMENT</td><td class='track'>AVG UP</td><td class='track'>AVG DOWN</td><td class='track'>BIGGEST UP</td><td class='track'>BIGGEST DOWN</td><td class='track'>LAST CHANGE</td><td class='one'>COMMISSION</td><td class='one'>CODE</td></tr>";
 
// set autobuy trip value 
//$count= 1; 
 
// select all data from tracker table and order from by highest movement to lowest movement
 
$sql = "SELECT * FROM `tracker` ORDER BY movement DESC";
$qry= mysql_query($sql);
echo "$qry";
while ($row= mysql_fetch_array($qry)) // this is line 21 ****************************************************
{
$code= $row['code'];
$current= $row['25'];
$up= $row['ups'];
$down= $row['downs'];
$evens= $row['evens'];
$movement= $row['movement'];
$bigup= $row['bigup'];
$bigdown= $row['bigdown'];
$upcount= $row['upcount'];
$downcount= $row['downcount'];
$twentyfive= $row['25'];
$twentyfour= $row['24'];
$commission= 15; // edit this to be number of shares worht 20000 divided by .05

kingb00zer · July 4, 2016

I moved the echo of the variable $qry to a different place later in the loop and it displays this SELECT * FROM transactions WHERE code='artna'

artna is the code of the row which is displayed first after it has been ordered by movement. for some reason it fails to continue through the loop after the first iteration and it only does this when the autobuy script adds data into the transactions table which at no point alters or adds anything to the table tracker which im trying to display.

Ingolme · July 4, 2016

I can't see that in any of the code you displayed. But the variable $qry should not be used for more than one thing.

kingb00zer · July 4, 2016

its only used in this section below to create a loop but only runs once when there is data in a completely different table and works fine when not. I am completely stumped on this one.

$sql = "SELECT * FROM `tracker` ORDER BY movement DESC";
$qry= mysql_query($sql);
 
while ($row= mysql_fetch_array($qry))

justsomeguy · July 5, 2016

You're using the same variable name for too many things.

displaying sql table using order by

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in