sptrsn

That works great. Thank you very much.

I want to use greater than or less than in my select statement depending on the user. I have no problem with the conditional statement and can echo the variable just fine. I just can't figure out how to use it in the select statement. For context, here is a simplified condition. (which is working fine) if ($user_id == 50) { $oper == '<'; } else { $oper == '>'; } The problem comes when trying to use that variable ($oper) in the select statement. $sql = "select * from t1 where v1 '$oper' v2"; Can some kind soul tell me what I'm doing wrong, or point me to some documentation? Thanks a bunch. mysql v 5.0.51a php5.2.4

That works beautifully! Thank you very much.

ok. I'm a total noob, but this is actually one that I can answer because I constantly fought a similar problem, so I'm kind of excited to finally give back. The max() function will only return a value, the highest value, but if you're like me, you assume it will bring you the entire row with the highest value. It doesn't. I gives you a simple value. At that point, you have to select the row that matches that value. Only after you've selected the row can you join it with other tables. select * from t2 as t2 join (select col2, max(id) as id from t2 group by col2)as mx on t2.id=mx.id Hope this helps.

During a select statement, I was doing a lookup on the fly, checking to see if there was a matching value in a second table. The tables have become large enough that it's causing huge speed issues. I don't need any data from the second table. I just need to know if a matching value exists. So, I created a "tracking" field in table 1. My plan was to periodically do an update query and simply set a "1" or a "0" in the tracking field in the first table, IF there was a matching value in the second table. Then I could just add some conditional formatting or whatever, to have a visual clue that table 2 has data for me. I can't figure out how to do it. I've been searching for examples for two hours and can't seem to figure it out. Could one of you, who are obviously smarter than me, give me a clue how to go about this? I've tried all kinds of retarded stuff. It seems to me that it would be something like this... (but obviously these doesn't work) UPDATE t1 SET `match` = '1' WHERE t1.apn = t2.apn or this update t1 set match ='1' where select * from t1 as t1 join(select * from t2)t2 on t1.apn=t2.apn

Wow Keith! thank you so much for that. That helped in several ways. to your point... it seems like anytime I have another field in the subquery, it is completely unrelated. That doesn't make a lot of sense. Thanks for taking the time to understand and reply! pm if you do any freelance.

Just when I thought I had this figured out, some combination of data comes up and whad'ya know? it doesn't work again. Here is the data id buyer apn bid bidstatus 1 61 10101120 77000 ACTIVE 2 62 10101120 99000 CANCELLED 3 63 10101120 75000 ACTIVE 4 62 10115374 99000 ACTIVE 5 62 10206459 75000 ACTIVE I want the highest "active" bid for each apn. With this data, that should be row 1, 4 and 5 I have tried everything I know and been searching for hours. Here's what I have that doesn't work... select id, buyer_id, apn, bid, bidstatus from prop p inner join (select max(bid)b from prop group by apn)m on p.bid=m.b where bidstatus='active' Here is the result of that query... id buyer_id apn bid bidstatus 3 63 10101120 75000 ACTIVE 4 62 10115374 99000 ACTIVE 4 62 10115374 99000 ACTIVE 5 62 10206459 75000 ACTIVE Wrong in multiple ways. What the heck? Can anyone help me with this?

I got it. Thanks. select * from nvcdata n join (SELECT* from prop d inner join (SELECT max(bid) b FROM `prop` GROUP BY apn)p on d.bid=p.b where bidstatus='active')z on n.apn=z.apn had to get rid of the that extra apn field select inside the subquery.

Ok, I finally figured out a way to use the max() function and get the right result and this query works great. Now I need to join it with another table, but I keep getting a duplicate column name error. specifically 'apn' here's my query that works... SELECT * from prop d inner join (SELECT apn, max(bid) b FROM `prop` GROUP BY apn)p on d.bid=p.b where bidstatus='active' I want to join another table on the apn number. Here's what I have that gives me a duplicate column name 'apn' Line 2 is the subquery that works, alias 'z' select * from nvcdata n join (SELECT* from prop d inner join (SELECT apn, max(bid) b FROM `prop` GROUP BY apn)p on d.bid=p.b where bidstatus='active')z on n.apn=z.apn I would sure be grateful for any insight you might be able to offer. Up to and including, "What? Are you nuts? You can't do that." It's only ok to say that if you'll rewrite it for me though.

I need to join three tables where t1.apn=t2.apn=t3.apn. apn is already unique in table 1. Tables 2 and 3 are sub-queries to get to a unique apn. My sub-queries seem to work fine. T2... select *, max(id) from nvcdata_history as T2 group by apn and T3... select *, max(bid) from prop as T3 group by apn So, at the end of these two sub-queries, apn is unique in all three tables. T1 is really the master list. (10k rows) T2 is a history of changes. So there is a row inserted each time there is an update. (30k rows) T3 is the small list of records I'm interested in fetching in this query. (100 rows) Here is what I think should work. (obviously it doesn't or else I wouldn't be here) select * from nvcdata as T1 join (select *, max(id) from nvcdata_history as T2 group by apn)on T1.apn=T2.apn join (select *, max(bid) from prop as T3 group by apn) on T2.apn=T3.apn Most common error is #1248. Every derived tale must have it's own alias. Although I have had a plethora of others. I don't recall which version. It's as recent as godaddy gets. (It sucks, but it's temp) At the end of this, I should have the 100 records from T3. The 100 matching records from T1. And the 100 matching records from T2, where the record that is returned has the highest id (hence the most recently inserted/updated record) Any bright ideas out there? Speed will count here. I have another query that is not dissimilar and it takes 3-5 seconds. That's an eternity when your staring at your computer. Thanks for any help.

This looks very promising. I don't know why I couldn't find it before. I only looked for a day and half. Thanks for that.

yea, that was what I tried, but it returns one single record. I think that there is somehow a query within the query. Where in the inner query, we query T2 and sort and limit 1 per apn, then the it's joined with the matching apn from T1. My coder had written this for something else and I got the impression that it would somehow be similar. select * from `T1` where apn in ( select apn from `T2` group by apn having count(*) > 1) order by apn

that didn't quite work. the goal is to have one record for each apn joined with only the most recently updated matching apn in T2. I was told that it would somehow involve sorting T2 then limit 1. But if I add limit 1 to the end of that statement, I get one record total. I need one row for each unique apn. Thanks.

Thanks so much for the reply. I'll give it a go.

I have two tables, that between them contain a complete record on property foreclosure data. (apn, adddress, sale date, open bid etc). apn is unique in T1 and the data in T1 is the data that does NOT change. (ie apn and address) In T2, we are storing the data that changes as time progresses. (ie sale date and open bid) Each time it changes, we insert this newest data into T2, along with a date/time stamp and of course the related apn. So, in T1, there is a unique record for each 'apn' with static data related to that property. (stands for assessors parcel number) However, T2, there are several records with the same 'apn', differing values for sale_date and or open_bid, and a date/time when it was inserted. I need a query that will join on T1.apn=T2.apn. However, I need the most recent matching T2.apn based on the 'update_date'. I know this is not even close, but I have no idea how to get there from here. select * from T1 join T2 on T1.apn=T2.apn Sure would appreciate any help you feel inclined to share. Thanks

Very cool. I have some studying to do. I've never seen some of what you're used here. Thanks. I'll get to work.

Currently, I insert a small thumbnail image at the begging of each row in a set of records from a mysql query. But that image is the same image for each record in that array. Is there a way to conditionally change which image is used, based on a value either in that same table, or a related table. See attached screenshot... and here's my current code.... <?php $query = "select address from nvc"; $result = mysql_query($query); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $details=''; $details.='<li class="menu"><a href=detail.php?address='.(urlencode($row['address'])).'><img src="thumbs/house.png" /><span class="name">'.$row['city'].'</a>'; $details.='</li>'; echo($details); } ?> Thank you for any input. [attachment deleted by admin]

Thank you very much Pikachu2000. I got it working with your input and learned a few things at the same time. that's a Beautiful thing! Here's what I have.... <?php $query = "select * from daily where id='$id'"; $result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_array($result) or die(mysql_error()); echo '<ul class="pageitem">'; echo '<li class="menu"><span class="name"><a href="http://maps.google.com/maps?q='.$row['address'].', '.$row['city'].'">'.$row['address'].', '.$row['city'].' </a></li>'; echo '<li class="menu"><span class="name">APN:#'.$row['apn'].' -- '.$row['subdivision'].'</li>'; echo '<li class="menu"><span class="name">SqFt: '.$row['sqft'].'('.$row['level'].') Built: '.$row['yr_built'].' Pool: '.$row['pool'].' Lot: '.$row['lot'].' </li>'; echo '</ul>'; echo '<ul class="pageitem">'; echo '<li class="menu"><span class="name">Open Bid: $'.number_format($row['open_bid'],0,'.', ',').'</li>'; echo '<li class="menu"><span class="name">$/SqFt: $'.number_format($row['price_sqft'],0,'.', ',').'</li>'; echo '<li class="menu"><span class="name">Equity: $'.number_format($row['equity']*100,0,'.', ',').'%</li>'; echo '<li class="menu"><span class="name">NVC Value: $'.number_format($row['est_value'],0,'.', ',').'</li>'; echo '<li class="menu"><span class="name">AVM: $'.number_format($row['avm'],0,'.', ',').'</li>'; echo '</ul>'; echo '<ul class="pageitem">'; echo '<li class="menu"><span class="name">'.$row['trustee_name'].' -'.$row['time'].'</li>'; echo '</ul>'; ?> And while my mailto line is less than elegant... it works. <a href="mailto:steve@myemail.com?subject=INFO REQUEST-- <?echo $row['address'];echo ', ';echo $row['city'];echo '-- '; echo date("l F d, Y, h:i A");?>"> Every time I treid to concatenate those variables in the mailto, it threw some kind of error. So I'll just role with this for now. Thanks again.

could you help me format this into a shorter statement. This works, but it's pretty ugly. <?echo $row['address'];echo ', ';echo $row['city']; echo '-- '; echo date("l F d, Y, h:i A");?>

Yes I did thank you. I stripped out all the "while loop" stuff, echo'd some simple stuff then got the mailto part working. Now I'm trying rebuild the array formatting since it's completely different that what I've been using. Making progress. Thanks again.

You are correct. I am only fetching one record from the database. It hadn't even occurred to me that I didn't need to use the while loop. I'm obviously not a programmer, so I just kept reusing code that worked for on other pages. I'm not even sure how to call for the data if I don't use my "tried and true" little snippet of code. But I'm willing to try. Perhaps that way, as you suggest, I can have the variable available anywhere on the page. I'll give it a try. Thank you.

yes. The array is the result of a db query. Here's the query.. <?php $query = "select * from daily where id='$id'"; $result = mysql_query($query); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $details='<ul class="pageitem">'; $details.='<li class="menu"><span class="name"><a href="http://maps.google.com/maps?q='.$row['address'].', '.$row['city'].'">'.$row['address'].', '.$row['city'].'</a></li>'; $details.='<li class="menu"><span class="name">APN:#'.$row['apn'].' -- '.$row['subdivision'].'</li>'; $details.='<li class="menu"><span class="name">SqFt: '.$row['sqft'].'('.$row['level'].') Built: '.$row['yr_built'].' Pool: '.$row['pool'].' Lot: '.$row['lot'].' </li>'; $details.='</ul>'; $details.='<ul class="pageitem">'; $details.='<li class="menu"><span class="name">Open Bid: $'.number_format($row['open_bid'],0,'.', ',').'</li>'; $details.='<li class="menu"><span class="name">$/SqFt: $'.number_format($row['price_sqft'],0,'.', ',').'</li>'; $details.='<li class="menu"><span class="name">Equity: $'.number_format($row['equity']*100,0,'.', ',').'%</li>'; $details.='<li class="menu"><span class="name">EstValue: $'.number_format($row['est_value'],0,'.', ',').'</li>'; $details.='<li class="menu"><span class="name">AVM: $'.number_format($row['avm'],0,'.', ',').'</li>'; $details.='</ul>'; $details.='<ul class="pageitem">'; $details.='<li class="menu"><span class="name">'.$row['trustee_name'].' -'.$row['time'].'</li>'; $details.='</ul>'; echo($details); } ?> Then, at the bottom of the page, I have a pop up window that I am using to generate emails for different purposes, where I have the subject line, and I want to append it with the address of the property on that page. thusly... <ul class="pageitem"> <li class="menu"><a class="noeffect" onclick="iWebkit.popup('popup1')">Select an Actvity</span></a></li> </ul> <div id="popup1" class="popup"> <div id="frame" class="confirm_screen"> <span>Activities</span> <a href="mailto:steve@myemail.com?subject=Request Info on.....ADD ADDRESS VARIABLE HERE!!....."> <span class="gray">Request Info</span></a> <a class="noeffect" onclick="iWebkit.closepopup(event)"><span class="black">Cancel</span></a>

I have an array of property details that prints to the page. Later on that same page, I would like to use one of the array's values as part of a mailto script, but I can't seem to figure out how to recall it. could someone point me in the direction of how I can make the array's values available for later use? I hope this is clear enough. Thanks

thank you. That and a little sleep does wonders. urlencode worked. Here's where I changed... <?php $query = "select DISTINCT city from daily where open_bid>0 order by city ASC"; $result = mysql_query($query); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $details=''; $details.='<li class="menu"><span class="name"><a href=list.php?city='.(urlencode($row['city'])).'>'.$row['city'].'</a>'; $details.='</li>'; echo($details); } ?> Thanks again.

I'm an hour worth of searches into this. It's 2:30. I just want some nice person to give me the answer. I'm trying to pass a variable using href to another page. It works great if the variable is only one word. But it only passes the first word. There are multiple posts on this topic, but I can't seem to get any of those fixes to work for me. Here the code. <?php $query = "select DISTINCT city from daily where open_bid>0 order by city ASC"; $result = mysql_query($query); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $details=''; $details.='<li class="menu"><span class="name"><a href=list.php?city='.$row['city'].'>'.$row['city'].'</a>'; $details.='</li>'; echo($details); } ?> As you can imagine, some cities names are made up of two words. This is only passing the first word. Thanks for your help.