Jump to content
MakeWebGames

error after error getting query


boionfire81

Recommended Posts

I need to select 1 row for each stockID with the result being the lowest price.

$q=$db->query("SELECT `stockID`, `stockNAME`, `stockPRICE` FROM `shares`  WHERE `stockSELL`> 0 GROUP BY `stockID` ORDER BY `stockPRICE` ASC");
while($r=$db->fetch_row($q)) {

 

The where stockSELL does fine. But if there are 2 people selling the stock it list every result for that. >.< I never used group, so this is probably where the error is.

Link to comment
Share on other sites

It is recommended to only use the GROUP BY clause when you are using an aggregate function within your query. Since I cannot see the error message on your post, I cannot confirm that this is the problem, but I have a feeling that's why this query is failing. For this query, there's no need for the GROUP BY clause, so just remove it.

EDIT: Also, if there are multiple people selling the stock and you only want 1 row, use the DISTINCT keyword in your query. Not 100% sure what results you are trying to get here (post is rather vague), but maybe that's it?

~G7470

Link to comment
Share on other sites

The distinct I think might be more of what I need. Each member can hold several different stocks. A row is created for each user and their individual stocks. Then there is a column that sets the amount of shares they are willing to sell and how much they are willing to sell for.

I need to select the lowest price of each stock.

What I have now is simply displaying every single row in the table being queried.

 

$q=$db->query("SELECT DISTINCT `stockID`, `stockNAME`, `stockPRICE` FROM `shares` WHERE `stockSELL`> 0 ORDER BY `stockPRICE` ASC");
while($r=$db->fetch_row($q)) {
Link to comment
Share on other sites

In that case, DISTINCT is "kind of" what you need, but it doesn't solve your problem yet. What you need to do is make a nested query. By doing this, you can then get the minimum value for that particular stockID, and thus be able to only get 1 row per stock. The query would look something like this:

 

SELECT DISTINCT st1.stockID, st1.stockNAME, st1.stockPRICE FROM shares st1.WHERE st1.stockSELL > 0 and st1.stockPRICE = (SELECT MIN(st2.stockPRICE) FROM shares st2 WHERE st2.stockID = st1.stockID) ORDER BY st1.stockPRICE ASC

 

I left the DISTINCT in there because in case that multiple people are selling the stock for the same price, only 1 row for that stock will be pulled instead of multiple rows.

Nested queries are important for solving more complex query issues, so now is a good time to start learning about how they work with the example I gave you for this issue.

~G7470

  • Like 1
Link to comment
Share on other sites

dude worked perfect (outside of the . next to WHERE). Thanks!

I'mma have to read up on the NESTED queries, cause that ^ I can't even read lol.

Ok, maybe not 100%. I also need to run a query to pull the last price from stocks.

So code for first stock page

 

$q=$db->query("SELECT DISTINCT `st1`.`stockID`, `st1`.`stockNAME`, `st1`.`stockPRICE` FROM `shares` `st1` WHERE st1.stockSELL > 0 and st1.stockPRICE = (SELECT MIN(st2.stockPRICE) FROM shares st2 WHERE st2.stockID = st1.stockID) ORDER BY st1.stockPRICE ASC");
while($r=$db->fetch_row($q)) {
$s=$db->query("SELECT `last` FROM `stock` WHERE `stockID` = {$r['stockID']} ORDER BY `time` ASC LIMIT 1");
$l=$db->fetch_row($s);

 

After quick check though my time() insert isn't working on purchase. Ok, researching now.

Tried UNIX_TIMESTAMP everything I could think. The time always seems to insert as 0000-00-00 00:00:00 no matter how I write it.

ONLY thing that worked is NULL... but why?

Edited by boionfire81
Link to comment
Share on other sites

My guess is that your time column in your stock table is using a TIMESTAMP data type, which if you want to use time() or UNIX_TIMESTAMP, that will not work.

Those times are generating a "unix timestamp", which is of an INTEGER data type. So, to use those, you have to set your time column to be an INTEGER data type.

~G7470

Link to comment
Share on other sites

I personally prefer using unix timestamps because PHP and MySQL make it very easy to create and use them. However, that doesn't mean that what I prefer is the "preferred method" across the board. It's really what you would prefer, as it is your database and game.

~G7470

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...