iainlang

Gordon Bennet, that was quick! I didn't even have time to turn round. As always, the answer was staring me in the face, but you pointed me to it. Many thanx.

Please, how do I turn a string whose contents equal, say, "2008-09-05" into "5th September 2008"? I've visited php.net but am just going round and round. (Is there any difference between the php4 and the php5 code?) TIA. Iain.

I've inherited the database of a charity whose volunteeers do Casework with the clients, and, as Caseworkers can occasionally be tackling more than one case at a time, I need to know the last Case number done by each Caseworker. I've never worked with MySQL's MAX() before and I'm haviing notta lotta luck. My (pretty naive, I guess) code follows. The "personnel" fields include - firstname, surname, based, position, telephone, mobile, email The "clients" fields include - caseworker_firstname, caseworker_surname, case_number $count=1; $TableName="personnel"; $Query="SELECT * FROM $TableName WHERE based=\"P\" AND position LIKE \"%Caseworker%\" ORDER BY surname desc, firstname desc"; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { $firstname[]=$Row[firstname]; $surname[]=$Row[surname]; $telephone[]=$Row[telephone]; $mobile[]=$Row[mobile]; $email[]=$Row; $count++; } $original_count=$count; print("<table width=\"900\" cellspacing=\"0\" cellpadding=\"2\" border=\"1\">"); print("<colgroup span=\"6\"><col width=\"17%\"><col width=\"5%\"><col width=\"14%\"><col width=\"14%\"><col width=\"14%\"><col width=\"35%\"></colgroup>"); print("<tr bgcolor=\"#ffffee\" align=\"center\"><td>Caseworker</td><td>Total</td><td>Last Case done</td><td>Telephone</td><td>Mobile</td><td>e-mail</td></tr>"); $TableName="clients"; while ($count>0) { $Query="SELECT max(case_number) AS maximum FROM $TableName WHERE caseworker_surname='$surname[$count]' AND caseworker_firstname='$firstname[$count]' ORDER BY caseworker_surname desc, caseworker_firstname desc"; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { $maximum[]=$Row[maximum]; } $count--; } $count=$original_count; while ($count > 0) { print("<tr bgcolor=\"#ffffee\"><td align=\"right\">$firstname[$count] $surname[$count] </td><td>$count</td><td><a href=\"cli_update_handle.php\">$last_case_number[$count]</a></td><td align=\"right\">$telephone[$count]</td><td align=\"right\">$mobile[$count]</td><td align=\"right\"><a href=\"mailto:$email[count]\">$email[$count]</a></td></tr>"); $count--; } print("</table>"); Any help would be appreciated. Thanx, Iain.

Friendly bump.

Thank you for your swift response. I do check the PRIMARY BOX radio button, always did, but still get the same error message.

I've searched the Knowledge Base but couldn't find anything which answered my question. I have an auto-increment id field in my tables which (for reasons of displaying the unsigned zerofill in limited screen space) I want to replace with a new auto-increment id field which will, of course, start again at 0. In phpMyAdmin, I delete the existing auto-increment id field and, when trying to install a new one, I get - "ALTER TABLE `help` ADD `id` SMALLINT( 2 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT FIRST MySQL said: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key " I expect it's something terribly simple and I'm just being stupid. Can anyone help, please?

Magic! Abso-bally-lutely magic! I think I'll start a Feast Day in your honour, name my seventeenth-born after you, raise a graven image, declare a Public Holiday, etc. Dunno how you guys do it. Big thanx.

I've done - include("php-lib/connect_inc.php"); $section_name=trim (strtolower($section_name)); $section_name=mysql_real_escape_string($section_name); // <<----- ; $section_text=nl2br($section_text); $TableName="sections"; $Query="INSERT INTO $TableName (section_id, section_name, section_vis_name, section_heading, section_text, image_filename, title_text) VALUES ('$section_id', '$section_name', '$section_vis_name', '$section_heading', '$section_text', '$file_name', '$title_text') "; $Result=mysql_db_query ($DBName, $Query, $Link) or die(mysql_error()); // <<----- ; include("sec_add.php"); which seems to be accepted wihout question, and $TableName="images_sub_sections"; $TableName="sections"; include("php-lib/connect_inc.php"); $Query="SELECT section_name, section_vis_name FROM $TableName WHERE section_id='$section_id' "; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { $section_name=$Row[section_name]; $section_vis_name=$Row[section_vis_name]; } $sub_section_name=trim(strtolower($sub_section_name)); $sub_section_text=nl2br($sub_section_text); $TableName="sub_sections"; $Query="INSERT INTO $TableName (sub_section_id, section_name, section_vis_name, sub_section_name, sub_section_vis_name, sub_section_heading, sub_section_text, image_filename, title_text) VALUES ('$sub_section_id', '$section_name', '$section_vis_name', '$sub_section_name', '$sub_section_vis_name', '$sub_section_heading', '$sub_section_text', '$file_name', '$title_text')"; print("$Query< br >"); // <<----- ; $Result=mysql_db_query ($DBName, $Query, $Link) or die(mysql_error()); // <<----- ; include("sub_sec_display_short.php"); which produces the line - INSERT INTO sub_sections (sub_section_id, section_name, section_vis_name, sub_section_name, sub_section_vis_name, sub_section_heading, sub_section_text, image_filename, title_text) VALUES ('4000', 'whats_on', 'What's On', 'test_sub_section', 'Test Sub-section', 'Test Sub-section heading', 'Test Sub-section text.', '', '') You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's On', 'test_sub_section', 'Test Sub-section', 'Test Sub-section heading', 'Test' at line 1 Dunno if that makes any sense to you; it doesn't to me.

Sorry - I should have answered your question. The only error I get is that the Sub-section doesn't get inserted into the database table.

Thanx for your swift response. Do you mean like - include("php-lib/connect_inc.php"); $section_name=trim (strtolower($section_name)); $section_name=mysql_real_escape_string($section_name); $section_text=nl2br($section_text); $TableName="sections"; $Query="INSERT INTO $TableName (section_id, section_name, section_vis_name, section_heading, section_text, image_filename, title_text) VALUES ('$section_id', '$section_name', '$section_vis_name', '$section_heading', '$section_text', '$file_name', '$title_text')"; $Result=mysql_db_query ($DBName, $Query, $Link); include("sec_add.php"); 'cos if that's it, it doesn't seem to work. I use HotDog and mysql and php functions are automatically coloured but this doesn't happen with the mysql_real_escape_string thing-y. Sorry to be so thick. Yoooors, Iain. [ I sent phpfreaks 10 sterling at the e-mail address donate@thewebfreaks.com - dunno what that comes to in dollars. ]

Hello. [ BTW - After reading "Have you been helped here?" I've been looking for a "Donate" button but I can't find one that doesn't just take me back to the Home page. Can anyone point me to the right URL? ] Now... I wrote my own small, very simple, naive, CMS scripts to help small charities run their own database-driven website without further aid from anyone outside their organisaton. I have about thirty now and all has been working well for about four years but one theatre Trust wants a What's On section (note the apostrophe - oddly enough, the first time any of "my" little charities thought to use one) and it seems that this forbids the creation of Sub-sections within any Section whose title contains an apostrophe. I've listed my scripts below; can anyone tell me what the solution might be to the apostrophe problem, please? I'm sort'v self-taught and am probably missing something stupid. Thanx. Iain. include("php-lib/connect_inc.php"); $section_name=trim (strtolower($section_name)); $section_text=nl2br($section_text); $TableName="sections"; $Query="INSERT INTO $TableName (section_id, section_name, section_vis_name, section_heading, section_text, image_filename, title_text) VALUES ('$section_id', '$section_name', '$section_vis_name', '$section_heading', '$section_text', '$file_name', '$title_text')"; $Result=mysql_db_query ($DBName, $Query, $Link); include("sec_add.php"); $TableName="images_sub_sections"; $TableName="sections"; include("php-lib/connect_inc.php"); $Query="SELECT section_name, section_vis_name FROM $TableName WHERE section_id='$section_id' "; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { $section_name=$Row[section_name]; $section_vis_name=$Row[section_vis_name]; } $sub_section_name=trim(strtolower($sub_section_name)); $sub_section_text=nl2br($sub_section_text); $TableName="sub_sections"; $Query="INSERT INTO $TableName (sub_section_id, section_name, section_vis_name, sub_section_name, sub_section_vis_name, sub_section_heading, sub_section_text, image_filename, title_text) VALUES ('$sub_section_id', '$section_name', '$section_vis_name', '$sub_section_name', '$sub_section_vis_name', '$sub_section_heading', '$sub_section_text', '$file_name', '$title_text')"; $Result=mysql_db_query ($DBName, $Query, $Link); include("sub_sec_display_short.php");

Aaaarrggghh! I forgot to change the $TableName for the second query; no wonder it wouldn't work. (I shouldn't be allowed out alone, really...) Thanx, Teenfront.

Hello. Some FOOL told a local charity that I was just the boy to take over their web-site, and an even bigger fool said "Yes" when I was asked to do it. So... I've inherited this charity site whose volunteers do Casework among its clients. The task is to list the Caseworkers in alphabetical order with the total number of Cases that each has done throughout the past year. I've tried messing around with GROUP BY and AS and stuff, but I just get deeper and deeper into the Coding Swamp. Unfortunately, I can't change the MySql database structure. Perhaps one of the gurus here can help? <?php include("connection_string"); $n="0"; $year=date("Y"); $year--; $start_date=$year."-".date("m")."-".date("d"); $TableName="personnel"; $Query="SELECT firstname, surname, telephone, mobile, email FROM $TableName WHERE position LIKE \"%Caseworker%\" AND based=\"P\" AND date_case_added >= $start_date ORDER BY surname, firstname "; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { $surname[]=$Row[surname]; $firstname[]=$Row[firstname]; $telephone[]=$Row[telephone]; $mobile[]=$Row[mobile]; $email[]=$Row[email]; $count++; } $count--; $original_count=$count; while ($n<=$count) { $Query="SELECT firstname, surname AS name FROM $TableName WHERE position LIKE \"%Caseworker%\" AND based=\"P\" AND date_case_added >= $start_date GROUP BY name ORDER BY surname, firstname "; $Result=mysql_db_query ($DBName, $Query, $Link); $num=mysql_num_rows($Result); $cases_done[]=$num; } $n="0"; print("<table width=\"800\" cellspacing=\"0\" cellpadding=\"2\" border=\"1\">"); print("<colgroup span=\"5\"><col width=\"8%\"><col width=\"30%\"><col width=\"25%\"><col width=\"23%\"><col width=\"17%\"></colgroup>"); while ($n<=$original_count) { print("<tr bgcolor=\"#ffffee\"><td>$cases_done[$n]</td><td align=\"right\">$firstname[$n] <strong>$surname[$n]</strong> </td><td align=\"right\">$telephone[$n]</td><td> $mobile[$n]</td><td> <a href=\"mailto:$email[$n]\" style=\"text-decoration:none;\">$email[$n]</a></td></tr>"); $n++; } print("</table>"); include("php-lib/footer_admin.php"); ?> Any help would be greatly appreciated. TIA. Iain. (edited by kenrbnsn to add tags)

Nope, it can't be that becos mysql_db_query works everywhere else. I see that my query didn't make it through unscathed - I should have written - $comments=$comments_old." br_within_angle-brackets ".$comments_new;

When trying to update a table, the following works only occasionally; it doesn't work reliably. Can anyone show me where I'm going wrong, please? // $comments_new is the data incoming from the form ; $Query="SELECT comments FROM clients WHERE id='$cli_update_id' "; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { $comments_old=$Row[comments]; } $comments=$comments_old." <br>".$comments_new; $Query="UPDATE clients SET comments='$comments' WHERE id='$cli_update_id' "; $Result=mysql_db_query ($DBName, $Query, $Link); TIA.

Someone told a local charity I know about coding web sites(!) and I've been asked to help. The following one-liner sends mail from a Linux server but not from their Windows server. mail ("admin@mydomain.com", "Subject", "body"); What do I need to check / change, please? TIA.

Ok, if we look at it another way, can someone guide me as to how I can check with php as to whether any character in a string is - ascii, or not, and letter, or number. TIA.

Thanks to everyone for helping in this matter. Alas, we're still not there. I think the best thing I can do is offer examples. Here goes - There are five cities in the UK which have a single-letter, and a single letter only, as the first part of their postcodes - Glasgow G Birmingham B Liverpool L Manchester M Sheffield S I'm going to stick with Sheffield and the London SW postcode for examples. Computers don't sort UK postcodes naively correctly for our purposes, and so - sw1 1aa sw10 1aa sw2 1aa sw20 1aa is not the sequence I need. However - sw 1 1aa (note the [space] after the "sw") sw 2 1aa (note the [space] after the "sw") sw10 1aa sw20 1aa come out in the order I need, and so that is how the postcodes are stored in the database (they are processed on the way in to produce that format, specifically so they can be sorted in the required order). The same applies to - s1aa s41 1aa s2 1aa s22 1aa and - s 1 1aa (note the [space] after the "s") s 2 1aa (note the [space] after the "s") s22 1aa s41 1aa The first $Query works great in that - SELECT * FROM database WHERE postcode LIKE "postcode%" when passed "sw" (i.e., two characters), returns - sw1 / sw10 / sw999 / etc and that is ideal. Unfortunately, when it is passed "s" (i.e., one character), it returns - sw1 /sw10/ sw999 etc AND s1 / s10 / s999 I'm hoping for code for a separate $Query which will, when passed a one-letter input, like "s" will ignore all the "sw..." variations and return only - s 1 1aa s41 1aa s 2 1aa s22 1aa whether or not the second character is a number or a space or even a NULL (I'm dealing with people with learning difficulties who can sometimes not recall even anything beyond the "s"; there is also the occasional typo). So the $Query has to return matches which use only the initial character, which must be a letter, and has to ignore anything which has a letter as the second character Like - sw[anything] (I'm hopeless at explaining things....) Can I do this with php in any way?

Thank you but the normal full postcode SELECT works with (minus the cosmetics) - $Query="SELECT distinct email FROM $TableName WHERE postcode like \"$postcode%\" "; For the first-letter-only operation, the code for the input page is - Choose the appropriate City - Birmingham <input type="radio" name="postcode" value="B"> Glasgow <input type="radio" name="postcode" value="G"> Liverpool<input type="radio" name="postcode" value="L"> Manchester <input type="radio" name="postcode" value="M"> Sheffield <input type="radio" name="postcode" value="S"> <input type="submit" name="submit" value="Search"> and the first-letter-only handling page that gets passed to is - $Query="SELECT distinct email FROM $TableName WHERE postcode REGEXP '^w+[\s0-9]+' "; which doesn't work. Boo-hoo. Can anyone see what I'm doing wrong?

The data is supplied by the people living at each address, so, thank you, but I am not trying to determine which address lies within which postcode and set the postcode accordingly. I have the information, I am looking on how to process it on the way out of the database.

I've inherited a php/mysql app which sorts and displays UK postcodes. When sorting, it uses SELECT... WHERE postcode like "$postcode%" and for a $postcode of, say E, this works Ok except it brings to screen everything starting EH (for Edinburgh) *and* everything starting E (for East London). I need to refine this further. How do I bring to screen *only* those starting, say, E (for East London), S for Sheffield and G for Glasgow, etc? I think I need to be able to ignore any postcode whose second letter is *not* a letter but is a number or space, or blank, but I don't know how to do this. Any help gratefully received.

Thank you for your swift answer. I'll give it a go over the week-end.

I'm trying to list meteorological data reports, from an ever-varying number of locations, in two columns, side-by-side - the first half of the reports listed alphabetically in the left column and then the second half of the reports listed in the right column so that the list runs alphabetically down the first column then goes to the top of the second column and continues down from there. In the example below, numbering the locations for the purpose of the example, it has to be something like this, using, say, 60 locations - <table> <tr><td>First column</td><td>Second column</td></tr> $Query="SELECT * FROM observations order by location"; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) ( <tr> <td>$Row[location1] $Row[humidity1] $Row[oktas1]</td><td>$Row[location31] $Row[humidity31] $Row[oktas31]</td> </tr> <tr> <td>$Row[location2] $Row[humidity2] $Row[oktas2]</td><td>$Row[location32] $Row[humidity32] $Row[oktas32]</td> </tr> } </table> and so on until the database is exhausted. I've tried using arrays to store each every second data item and then every other data item but I think it's clumsy. (It *IS* clumsy!) Is there a more elegant way to do it? TIA.

Following your reply, I changed the line to - $Query="SELECT * FROM $TableName where date_of_broadcast='$dates_of_broadcast[$n]' order by start_time"; Bless yer lil' cotton sox - that seems to have done the business.  Words like "close" and "trees" and "forest" come to mind here... Thanx!

I'm beginning to regret volunteering to help with the web site of a local independent radio station which has an upcoming temporary broadcasting licence...  They can't afford to hire someone who knows what he's doing, so I stupidly said "I'll do it".  Duh.... The database structure is - id | date_of_broadcast | start_time | finish_time | firstname | surname | show_title | data The following (which is minus all the cosmetics) produces only empty tables for each date. $TableName="schedule"; $Query="SELECT distinct date_of_broadcast FROM $TableName order by date_of_broadcast"; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { $dates_of_broadcast[]=$Row[date_of_broadcast]; } $count=count($dates_of_broadcast); $count--; $n=0; while ($n<=$count) { // get each day's date ; $broadcast_date=$dates_of_broadcast[$n]; $broadcast_date=date("jS F Y", strtotime($broadcast_date)); print("<table>"); print("<tr><td><>$broadcast_date</font></td></tr>"); // now get the programmes for each broadcast_date ?? ; $Query="SELECT * FROM $TableName where date_of_broadcast='$broadcast_date' order by start_time"; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array ($Result)) { print("<tr><td>$Row[start_time]</td><td>$Row[finish_time]</td><td>$Row[show_title]</td><td>$Row[firstname] $Row[surname]</td><td>$Row[data]</td></tr>"); } print("</table>"); $n++; } print("</td></tr></table>"); Any help gratefully received.  Thanks!