aschk
Staff Alumni-
Posts
1,245 -
Joined
-
Last visited
Never
Everything posted by aschk
-
If this is the firebird db then you have posted the question in the wrong forum...
-
Ah right, i'll explain. When using a LEFT JOIN for each row in the primary table you'll join a row in the secondary table EVEN IF NO MATCHING RECORD EXISTS. If no record exists you'll just get all the columns with NULL in them. for example, create 2 tables: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL ); CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL ); Insert some random data (so you have mismatching rows): INSERT INTO t1(id) VALUES(1),(2),(3),(4),(5); INSERT INTO t2(id) VALUES(1),(3),(5); As you can see from the above there are 3 matching records in the 2 tables. So now perform the statement: SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id; You'll see that for each row in t1 you get a corresponding row in t2, UNLESS there is no row, in case all the t2 columns will just have NULL in them. Therefore going back to the original statement, by doing "WHERE deriv1.caseworker_id IS NULL" grabs all the rows from the JOIN'ed tables that have NULL values from the JOIN. i.e. giving you the ones that DON'T have a corresponding record. Hope that helps clear things up a little.
-
It really does depend on what your defining as your field to determine the "last" record. SELECT MAX(auto_increment_column) WILL work however I don't recommend it because you're relying on auto increment working properly. If you don't have an auto_increment field you'll want to ORDER BY <defining column> LIMIT 0,1
-
SQL for all cases taken in the last 90 days: SELECT case_id ,caseworker_id ,caseworker_allocated as 'date_allocated' FROM TBLCASES c WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY) SQL for all caseworker ids for cases in the last 90 days: SELECT caseworker_id FROM TBLCASES c WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY) GROUP BY caseworker_id SQL for all caseworker ids for cases NOT in the last 90 days: SELECT caseworker_id FROM TBLCASES c LEFT JOIN (SELECT caseworker_id FROM TBLCASES WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY) GROUP BY caseworker_id) deriv1 ON c.caseworker_id = deriv1.caseworker_id WHERE dervi1.caseworker_id IS NULL SQL for all caseworker ids for cases NOT in the last 90 days + last worked case date: SELECT caseworker_id ,MAX(caseworker_allocated) as 'last_worked' FROM TBLCASES c LEFT JOIN (SELECT caseworker_id FROM TBLCASES WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY) GROUP BY caseworker_id) deriv1 ON c.caseworker_id = deriv1.caseworker_id WHERE dervi1.caseworker_id IS NULL GROUP BY caseworker_id n.b. I have not tested the above (because i'm too lazy to create a test db to do it) but with any luck it should provide you with what you need + the steps i took to get to the final solution.
-
Of course it'll only match the word you've specified in the query. i.e. you're looking for "dinosaur" it's only going to provide you with results that actually contain "dinosaur" NOT "dino", "dinos", "dinosaurs", etc It's not a google searching algorithm you know... it doesn't provide synonyms for words. n.b. there is a way to return results based on relevancy, so i recommend check the MySQL manual for it.
-
You will want to store the IP in BOTH forms. i.e. as a VARCHAR for readable/printable purposes and an INT so you can do numeric calculations using it. You're denormalising your data a little but using this structure you don't have to perform conversions on the INT version to get the printable version. This works well if you're doing a lookup on a range (i.e. between x < y) and you want all the readable addresses. structure: ip_numeric | ip_printable INT VARCHAR(15)
-
[SOLVED] Simple table-Getting results using one instead 2 queries
aschk replied to anthylon's topic in MySQL Help
Yuck using temp tables... Here's a single query solution: SELECT t1.* FROM my_table t1 JOIN (SELECT id, MAX(date) AS mdate FROM my_table GROUP BY id) as t2 ON t1.date = t2.mdate AND t1.id = t2.id -
[SOLVED] help with this query .. almost working
aschk replied to severndigital's topic in MySQL Help
A quicker way would be to perform it without the subquery, and instead using a LEFT JOIN on NULL. e.g. SELECT b.bowlName ,DATE_FORMAT( b.bowlDate, '%a %m/%d/%Y' ) AS bowlDate ,DATE_FORMAT( b.closing, '%m/%d/%Y %h:%i:%s%p' ) AS bowlClose ,ent.status AS userStatus FROM bowlList b LEFT JOIN bowlEntries ent ON b.Id = ent.Id WHERE ent.Id IS NULL -
Yes, store the information in a db, and every time the menu page (item, class, whatever) is called then you do a lookup on the date range in the db, and pull the relevant menu number ...
-
[SOLVED] Is this too hard for a noob to make work?
aschk replied to justAnoob's topic in PHP Coding Help
No, it's not hard. When you upload the file just get the userid from the session login (or wherever you have stored it) and insert it into a third table that contains the ids for the inserted images and the userid's. If you have no idea what i just said then maybe a 101 in db design would help you understand the structure, Google is your friend -
$softwareResult[OtherInfo] will give you PHP warning error in your logs for "Invalid array index" or something simliar. You MUST use quotes for associative array indices. i.e. $softwareResult['OtherInfo'] You also haven't shown the full code for the following line: <textarea name='otherinfo' id='otherinfo' cols='45' rows='5' value='$softwareResult[OtherInfo]'></textarea> We don't know how the above is being output? By echo? By print? timmah1's solution is the correct one. So either post the full code or implement his solution.
-
Trying to Put 4 seperate PHP scripts into a tabbed page, help anyone?
aschk replied to Charlie9809's topic in PHP Coding Help
So you're saying that you don't need PHP to build that tabbed page, which is what I thought. So just create an html page with 4 tabs in it. What's the problem. What is it you're trying to do? You seem to be confused between where PHP resides and what the client sees. PHP is server-side not client-side. So it processes things that you ask it to (by calling a script name), but doesn't necessarily present any output. -
The question is what do you require from your resultset? Because at the minute the results you will be getting will be the ones where rentpm is less than <amount> OR bedrooms greater than <beds> Notice the OR part of that statement, hence you get both the rows where the rentpm is less than amount, and the bedrooms are greater than beds, so you're getting both resultsets, but you're not limiting them by each other, the letting can either be less expensive than X OR have a number of rooms greater than Y. That's NOT lettings where both rules apply. Don't you want the lettings where the rent is greater than X AND bedrooms is greater than Y ?
-
[SOLVED] DB update not working - possibly just needs a 2nd pair of eyes
aschk replied to Merdok's topic in PHP Coding Help
Yup they do come from the $_POST, but you're using them before you've populated from $_POST. see line: if (empty($articleTitle) || empty($articleBody) || empty($pageName) || empty($meta_key) || empty($meta_desc)) You've not set those variables yet -
Yes but you need to decide how you're grouping those rows. i.e. SELECT GROUP_CONCAT(field_id SEPARATOR ',') ,GROUP_CONCAT(data SEPARATOR ',') FROM <table name here> GROUP submission_id
-
Assuming you do actually have empty rows i.e. all columns in the row are blank (the default empty string) then you can do // The following is pseudo-sql, fill in the required parts (< >) DELETE FROM <table name here> WHERE <column> = '' AND <column> = '' [AND <columnn = ''> [REPEAT!]]
-
Here's a tidier way of writing what you have: <?php // Copy $_POST into $data so we still have a raw copy. $data = $_POST; // Stripslashes and escape for SQL. array_walk($data, 'stripslashes'); array_walk($data, 'mysql_real_escape_string'); // Extract array values into local symbol table. // n.b. i don't ordinarily advocate the use of this function // because I personally believe it faces insecurities. extract($data); // Set up our SQL query string. $query = "UPDATE members SET msurname = '{$msurname}', mfirstname = '{$mfirstname}', address = '{$address}', suburb = '{$suburb}', pcode = '{$pcode}', home = '{$home}', work = '{$work}', mobile = '{$mobile}', email = '{$email}', WHERE member_id = $row[0] LIMIT 1"; ?> Notice that the variables must be enclosed in single quotes (') because you're inserting string values into a varchar column in your database.
-
I didn't realise that php came in 32bit and 64bit versions... i thought your operating system + processor defined those ;-)
-
[SOLVED] DB update not working - possibly just needs a 2nd pair of eyes
aschk replied to Merdok's topic in PHP Coding Help
You logic won't work because you're not populating the $pageID, $pageName, $articleTitle, $articleBody, $meta_key, $meta_desc variables in the elseif part of your statement. You're doing if (blah) populate variables (listed above) elseif(another blah) use variables populated... OOPS i haven't populated them in this part... -
Trying to Put 4 seperate PHP scripts into a tabbed page, help anyone?
aschk replied to Charlie9809's topic in PHP Coding Help
I think you're confusing fancy html & javascript with actual PHP. What you see there is facilitated by javascript tabbing. Each tab just contains a form, each of which posts to a different PHP processing page. Unless of course you're looking to do something different like dynamically build the html page from outputs from 4 different PHP scripts? -
[SOLVED] fwrite over ftp+wrote code+now need help
aschk replied to jakebur01's topic in PHP Coding Help
I'd like you to explain more thoroughly what it is you're attemping to achieve. Are you doing the following: 1) Reading a database table (for 100,000 rows). 2) Inserting each row into a file? The one thing that strikes me as rather odd is that you're writing over ftp. Which is rather silly. Write to a temporary file locally and then once you've finished that push it via ftp. Otherwise you're writing each line of the file across the network one at a time... <? // The following is psuedo-code: $tmpFile = 'tmp.txt'; $fh = fopen($tmpFile, "a"); $sql = "SELECT <rows from db>"; foreach($rows as $row){ fputcsv($fh, $row, '\t') // this is a handy PHP function! } fclose($tmpFile); $ftp_connect = ftp_connect("somedomain.com"); ftp_login($ftp_connect, "username", "password"); ftp_fput($ftp_connect, "remotefilename.txt", $fh = fopen($tmpFile)); fclose($fh); ftp_close($ftp_connect); ?> One more thing that strikes me as odd is that you seem have to the currency indicator inside the database field value, i.e. "$1.00". Normalize your data PLEASE! That should be 2 (or 3) columns for currency_symbol, currency_name, currency_value. -
Trying to download a file using curl...requires login
aschk replied to lordphate's topic in PHP Coding Help
If the subsequent pages require a cookie (as you would expect with a session) then you need to maintain cookies during the execution of your script, and pass them through each request. -
[SOLVED] concurrent update after select potential problem
aschk replied to rubing's topic in MySQL Help
I should point out that the query you are running will update EVERY row in your table, adding 1 to the num_views column for all the rows. I hope you're planning on sticking a WHERE clause on that to make it only update 1 row. -
As a side note: I have generated a function that will tell you if the $num is within a range. /** * Within function. * */ function within($num, $range = array()){ if(!$count = count($range)){ return false; } sort($range); return ($num >= $range[0] AND $num <= $range[$count-1]); } /** * EXAMPLE USAGE: constants */ if( within( 5, range( 3, 7) ) ){ echo "true"; } else { echo "false"; } /** * EXAMPLE USAGE: variables */ $x = 5; $y = $x -5; $z = $x + 5; if( within( $x, range( $y, $z) ) ){ echo "true"; } else { echo "false"; } Hopefully this will help someone.
-
Sessions are still persisting on your server, i guarantee you of that. However with the latest IE hot fix you might find that your cookie permissions have been altered. Sessions typically rely on cookies, and if they aren't being stored each new request to the server will result in another session being created. This is not a PHP problem, but a browser issue.