desoto0311

One other comment. I just double-checked and actually my den_postalcode row (where my zip codes are stored in the mySql db) are actually set as varchar. This is even more confusing then. Shouldn't the results simply be output basically as a string? Why would it search within a varchar row for results that don't start with the leading zero?

Not sure how the between operator would work for the first query? I can see how it could work for the second (thanks) but again, not so much the first, lol.

I have a sql query in php to return zip codes within a 'radius' (of sorts, lol) of +/- 15. (ie search in 65650 and it returns 65635 - 65616) The problem is the query, when searching for zip codes that start with '0' (zero) ie 01126 will also return zip codes starting with 11 (ie 11264) which is nowhere near the other locations. The row in the table is den_postalcode and is set as numerical. Here are my queries: $sql = 'SELECT * FROM tbl_dentists 'WHERE '.$countrycode.$city.$postalcode ORDER BY RAND() LIMIT 30 '; Second query: $postalcode = ' AND den_postalcode < "'.($postal+15).'" AND den_postalcode >"'.($postal-15).'"'; 'SELECT * FROM tbl_dentists 'WHERE '.$countrycode.$postalcode ORDER BY RAND() LIMIT 10 '; Soooo... I'm hoping this is enough info, but why is the returned result for zero's also including non-zeros? (BTW, since the zip/postals are also in Canada/elsewhere, the zip code can't be 'forced' using a 5 digit return.) Thanks in advance! -D

Cool thanks. Yeah, I'm *somewhat* versed in php arrays (I seem to use them more and more as time goes on) but I've got an existing site this is now going in that uses smarty templates. It almost seems harder sometimes as I've got to duplicate the results (ie setup the aforementioned foreach in php, then again later in smarty) although I understand the separation of code and content. Anyway, this works for me so I'm stoked. Thanks again, -D

Yep that did it. So the good news is no more looping of duplicate records. (Now I just get to figure out how to pull filenames from it's array, but that's not terrible.) The BAD news is that function had a double-use, which was to check if looking for a single record or multiple records. Now my multiple record search results only pulls the last record. Not a biggie though; I figure I'll just duplicate the entire "while" statement and use your modified version for my single result and the original for all other results. I think it's a 'band-aid', but WAY less band-aid-y than it was going to be originally, lmao. Thanks a bunch!

I should have probably just done this from the get-go, lol. Here's the complete table structure and function: Table Structure: table: mediaarticles (TABLE 1) L id L catid L title L author L desc mediafiles (TABLE 2) L id L articleid (This is 'id' from TABLE 1) L filename Function: function getMediaPages($catID,$mediaArticle) { $limit = 5; if (isset($mediaArticle)) { $sql = "SELECT * FROM mediaarticles, mediafiles WHERE mediaarticles.id = mediafiles.articleid AND mediaarticles.id = $mediaArticle"; } else { if ($catID != "all" && $catID != 0) { $sql = "SELECT * FROM mediaarticles WHERE catid = $catID"; } else { $sql = "SELECT * FROM mediaarticles, mediafiles WHERE mediafiles.articleid = mediaarticles.id ORDER BY mediaarticles.id DESC LIMIT $limit"; } } // check : search by category or article END $res = sqlQuery($sql); if(sqlErrorReturn()) sqlDebug(__FILE__,__LINE__,sqlErrorReturn()); $c=0; while ($a_row = sqlFetchArray($res)) { $records[$c]["id"] = $a_row["id"]; $records[$c]["catid"] = $a_row["catid"]; $records[$c]["title"] = $a_row["title"]; $records[$c]["author"] = $a_row["author"]; $records[$c]["desc"] = $a_row["desc"]; $records[$c]["articleid"] = $a_row["articleid"]; $records[$c]["filename"][] = $a_row["filename"]; ++$c; } if (!empty($records)) { return $records; } } In a nutshell, if "$ArticleId" var is there, it uses the first SQL query, and is *SUPPOSED* to return ONE record, from mediaarticles with the appropriate id ($ArticleId = mediaarticles.id) and then grab all mediafiles.filenames and place them into the filenames records array. I then have :: (smarty template) {if $mediaArticle} {foreach item="records" from=$records} Results Looped here. At this point I may need a second foreach(?) to pull from the filenames array? {/foreach} {/if} Which displays the records results. Seemed like it should work (lol) until I ran into multiple filename records.... :/ EDIT: As far as getting a raw SQL dump; sorry, don't know how to pull that one off. I paste my sql query but the results are still html printed tabular data. (And it DOES loop through the same record, in this case mediarticle.id "2", but attaches the different filename to it each time.)

Here are the results of the query. Looking at this, it appears that while the filename is an array, I don't have it setup properly to list all filenames in that array. (It's putting one name into each instance of the array.) I have the DB structure posted above; it's a pretty simple one but should work. Array ( [0] => Array ( [id] => 2 [catid] => 3 [title] => images test [author] => author name [desc] => description here. [articleid] => 2 [filename] => Array ( [0] => image0001.jpg ) ) [1] => Array ( [id] => 3 [catid] => 3 [title] => images test [author] => author name [desc] => description here. [articleid] => 2 [filename] => Array ( [0] => image5434.jpg ) ) [2] => Array ( [id] => 4 [catid] => 3 [title] => images test [author] => author name [desc] => description here. [articleid] => 2 [filename] => Array ( [0] => image5464.jpg ) ) )

I'm getting there, lol. At the moment I tend to be of more flash assistance than php/mysql, lol. Okay, so I took a look at this again and it *appears* that I might actually be having problems with either the initial SQL statement or possible the 'while' statement. The 'filename' records are in an array now, however when displaying (using : {foreach item="records" from=$records}) the loop, I still get a repeated record (same record from 'table1.id') times the number of filename entries. I thought something like that only happens if I do a LEFT or RIGHT join?

Sweet, I'll try it! BTW, do you answer ALL of the posts in this forum? lmao. When I go searching for answers around here I see your name come up quite a bit on the 'answer' side of things, lol.

I have two tables that I'm getting data from using join. After collecting this, I'm creating an array to use variables for php/smarty later. Everything works fine with this mySQL call, but I think I need to somehow put my 'filename' variables in another array inside my 'records' array. This is what I mean: myfunction ($ArticleId ) { //SQL statement: $sql = "SELECT * FROM table1, table2 WHERE table1.id = table2.articleid AND table1.id = $ArticleId";// $ArticleID pulled from URL. while ($a_row = sqlFetchArray($res)) { $records[$c]["id"] = $a_row["id"]; $records[$c]["articleid"] = $a_row["articleid"]; $records[$c]["filename"] = $a_row["filename"]; } return $records } // Note this isn't the exact function; I copy/pasted only this for brevity. In my two tables I have a bunch of data, but here are the important ones: table 1: id, title, date, author, desc table 2: id, articleid, filename Currently when I use a for each loop (smarty template but basically php) if there's only one 'filename', then it's fine, but if a particular recordset has multiple filenames, it loops (displays) the same 'article' with each filename. (Which I understand is what I'm asking the SQL statement to do.) So since this function actually does a few other things, I'm hoping to not have to cut it up too bad (lol) but basically need a way to have my output $records loop display only the one record with $ArticleID (in this example there's only 1 record that should ever display, so I'm sure my mySQL statement is somewhat wasteful.) but have the $record.filename output give me the list of only table2.filename results. I hope this makes sense.

I've got two tables laid out as such: table 1: "articles" L id L catid L title L author L desc table 2: "files" L id L articleid L filename I've got the SQL statement: $sql = "SELECT * FROM articles, files WHERE files.articleid = articles.id"; and a while statement to create my variables. (for later placement into a smarty template) while ($a_row = sqlFetchArray($res)) { $records[$c]["id"] = $a_row["id"]; // note: this repeats for all variables. } The problem is, when referencing said variables in the $records array, it seems I can only output those from the first table (articles). Those from the second table aren't seen at all. Am I missing something? ------------------------------- MySQL: v.5.0 PHP: v.5.2.5 Thanks, -D

Yes, I *mostly* get it, thanks. I did actually tie all three tables together by way of id's. Essentially table 2 is 'categories', and all data in table 1 MUST belong to one of the categories in table 2. Table 3 is a collection of 'other' information, with each data row belonging to a data row in table 1. I tried to name the rows so that it would make sense, but I don't think I did a great job of it, lol. Additionally, it looks like I might not have setup the tables in the most concise way. I *think* I should be able to make this work based somewhat on your code. After I get the SQL results I'm after then I'll confer with the SQL tutorial I've been using on this site to setup the loops necessary to display the stuff. Thanks a bunch!

I checked another site about 'subqueries' and this looks promising as well: SELECT id FROM table3 WHERE id IN (SELECT table1refid FROM table3 WHERE table1refid=table1.id); Would that be accurate? Of course this gets me back to the fact that this query really would need to be done AFTER all table1 records that match the table2 id's have been selected. It's still a 'double-query' type deal....

Oh, I see what you did there, makes sense. How would you pull off a 'sub-search' then for all items in table 3? Would you basically set the results of this SQL query as a variable (array?) that's then plugged into a second SQL query? Something like: SELECT * FROM table3 WHERE $arrayResult = something something something.... okay I lost it, lol.

SELECT DISTINCT id FROM table1, table2 WHERE table2.id=table2sub; Better? Or is this slightly reversed?