[PHP-DB] Possible solution to a simple newbie problem of JOINs
My newbie problem is solved and it may help other newbies if I share it. The idea is to take two or more tables and combine their data into one table that you can query. Each of the source tables may only have one or two columns of overlap. You want records (rows) that do not fill in data in unused fields, as JOINs do. (I tried JOINS and tried to get help on JOINS from these lists, but that's another story.*) What worked for me is: Create Table A and Table B, the source tables. Create Table C (Combination table) which will be used to collect the combined data. You can kill the records in this table regularly, as it is only a temporary storage area. Take Table A and INSERT SELECT it into the Combination table. Then take the Table B and INSERT SELECT it into the Combination table. In each of these passes, you can position the data into the columns that you decide, using the Select portion of the statement. The only requirement is that the data type be the same, so you must put an integer into an integer column, text into a text column, e.g. Also, I first use the TRUNCATE statement to kill all records in the Combination table before I load the data into it from Table A and Table B. That way, the Combination table is rebuilt with fresh data just before it is used. My problem was that I needed a database to keep track of a symposium's schedule, which includes about 21 Sessions, each of which has perhaps 10 Talks. It was necessary to make a separate table each for Sessions and Talks because I needed to provide my administrative client with the ability to change a Session title but not have it affect the Talks table, or vice versa. But, when all is said and done, the symposium's web page must deliver a schedule of a day's events that shows the Session title, then the Talks under it, then the next Session title, etc. First Table Name: Sessions ID int(6) autoincrement not null Session varchar(36) null SessionTitle varchar(255) null Sample rows: 1 Session I Dead Poets 2 Session II Live Poets 3 Session III Bad Poets Second Table Name: Talks ID int(6) autoincrement not null Session varchar(36) null StartTime time TalkTitle longtext null Sample rows: 1 Session I 07:00 Death of Smoochy 2 Session I 07:15 Byron's Secret 3 Session I 07:30 Kipling's Curse 4 Session II 10:00 Simon's Rhymes 5 Session II 10:15 AB Schemes Suppose you now want a table that we will call Combo, which gives you all the rows from Sessions and all the rows from Talks: Table Name: Combo ID int(6) autoincrement not null Session varchar(36) null SessionTitle varchar(255) null StartTime time TalkTitle longtext null Here is the SQL code to use: INSERT INTO Combo (Session, SessionTitle) SELECT Session, SessionTitle FROM Sessions (This loads from the table Sessions, into the appropriate columns of Combo) INSERT INTO Combo (Session, StartTime, TalkTitle) SELECT Session, StartTime, TalkTitle FROM Talks (This loads from the table Talks, into the appropriate columns of Combo) What we have assembled in Combo will look like this: 1 Session I Dead Poets nullnull 2 Session II Live Poets nullnull 3 Session III Bad Poets nullnull 4 Session I null07:00 Death of Smoochy 5 Session I null07:15 Byron's Secret 6 Session I null07:30 Kipling's Curse 7 Session II null10:00 Simon's Rhymes 8 Session II null10:15 AB Schemes (Notice that I always use an ID field as a primary key in every table, even though this data doesn't need to be passed into the Combo table. Whenever you rebuild Combo using TRUNCATE, you get new ID numbers for the rows.) I can now query the Combo table and use ORDER BY to sort the rows, getting exactly the results I need. (This is only a dummy set of data. The real symposium is completely different and the tables A, B and Combo all have a lot more fields.) This probably isn't a perfect solution. I will bet that there is a better way to do it and some smart guy can put it into one line of SQL code, but I had to get to the goal line like Clint Eastwood--any which way I could. --Dave Shugarts * If you're like me, you're not trying to be some expert in PHP or MySQL. You more or less have to work with PHP and MySQL because it came with your hosted server. So you read the documentation, which is really poor and has little sympathy for non-programmers. And you look for help on these two lists. People from each list tell you that you are asking the wrong question, whether it's a SQL question that has PHP code, or a PHP question that has SQL code. People on both lists tell you to RTFB. So you spend hours and hours figuring out what they could have just shown in a simple example. I think the really smartest guys know the line from Chaucer, Gladly would he learn and gladly teach. HTH -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
FW: [PHP-DB] error checking question
-- Forwarded Message From: David Shugarts [EMAIL PROTECTED] Date: Thu, 26 Jun 2003 18:53:53 -0400 To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] error checking question Hi, David-- The question you are asking belongs to the topic or concept called form validation and you will see lots of hits about it on a Google search. I have been looking at the same issue and I am impressed with one solution, http://www.x-code.com/vdaemon_web_form_validation.php. However, I am interested in what other people might think. With the help of this List, I have had some success in validation of one or two variables in a form, but this solution, vdaemon, represents an attempt to cover the complete spectrum of the variables people most often want to validate. I feel it may be on the right track (even though the one I wish to validate isn't covered). --Dave Shugarts Hello everyone, I've been experimenting with checking for errors in data inserted into a table using an html form. The main page displays the result of a select * from the_table at the top of the page, with the form for inserting the data beneath. The SQL for the insert occurs on a different page, with redirection back to the main page using the header() function ( as I've learned to do from reading this list ). This all works well. The inserted data shows up in the table, and there are no surprises when refreshing, etc... I have also implemented some error checking on the client side using JavaScript, and this is also working well. What I would like to do is somehow have a redundant error check on the server side and then display an error message above the form on the main page should fields be left blank or forbidden characters entered. Since I have access to the $_POST['variables'] on the form processing page, I've already written some code to detect the presence of error characters, etc...and no query is performed if there is an error. But how do I pass a flag variable back to the main page to echo an appropriate message above the form? Advice or suggestions greatly appreciated. David -- End of Forwarded Message -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Time input formatting in PHP-12 hour clock
With help from the List, I have had some success in answering my question about formatting 12-hr vs. 24-hr time and I thought I would share these two examples of what is working for me. The first example keeps the user from inputting invalid data. In the second, I note that an error yields a -1 but the string produced appears to be null. --Dave Shugarts /* *** EXAMPLE I ** */ html form Hr: SELECT NAME=hh SIZE=1 OPTION value=01 01 OPTION value=02 02 OPTION value=03 03 OPTION value=04 04 OPTION value=05 05 OPTION value=06 06 OPTION value=07 07 OPTION value=08 08 OPTION value=09 09 OPTION value=10 10 OPTION value=11 11 OPTION value=12 12 /SELECT nbsp; nbsp;Min: SELECT NAME=mm SIZE=1 OPTION value=00 0 OPTION value=01 01 OPTION value=02 02 OPTION value=03 03 OPTION value=04 04 OPTION value=05 05 OPTION value=06 06 OPTION value=07 07 OPTION value=08 08 OPTION value=09 09 OPTION value=10 10 OPTION value=11 11 OPTION value=12 12 OPTION value=13 13 OPTION value=14 14 OPTION value=15 15 OPTION value=16 16 OPTION value=17 17 OPTION value=18 18 OPTION value=19 19 OPTION value=20 20 OPTION value=21 21 OPTION value=22 22 OPTION value=23 23 OPTION value=24 24 OPTION value=25 25 OPTION value=26 26 OPTION value=27 27 OPTION value=28 28 OPTION value=29 29 OPTION value=30 30 OPTION value=31 31 OPTION value=32 32 OPTION value=33 33 OPTION value=34 34 OPTION value=35 35 OPTION value=36 36 OPTION value=37 37 OPTION value=38 38 OPTION value=39 39 OPTION value=40 40 OPTION value=41 41 OPTION value=42 42 OPTION value=43 43 OPTION value=44 44 OPTION value=45 45 OPTION value=46 46 OPTION value=47 47 OPTION value=48 48 OPTION value=49 49 OPTION value=50 50 OPTION value=51 51 OPTION value=52 52 OPTION value=53 53 OPTION value=54 54 OPTION value=55 55 OPTION value=56 56 OPTION value=57 57 OPTION value=58 58 OPTION value=59 59 /SELECT nbsp;nbsp;AM/PM SELECT NAME=ampm SIZE=1 OPTION value=AM AM OPTION value=PM PM /SELECT nbsp;nbsp; input type=submit /form ?php $testtime=$hh.:.$mm. .$ampm; echo Testtime = $testtimebrbr; $time_as_timestamp=strtotime ($testtime); echo Variable \testtime_as_timestamp\ = $time_as_timestampbrbr; $time_as_string=strftime(%I:%M %p, $time_as_timestamp); echo Variable \testtime_as_string\ = $time_as_string; ? /html /* *** EXAMPLE II ** */ html form Hr: INPUT TYPE=TEXT NAME=hh2 SIZE=6 nbsp; nbsp;Min: INPUT TYPE=TEXT NAME=mm2 SIZE=6 nbsp;nbsp;AM/PM SELECT NAME=ampm2 SIZE=1 OPTION value=AM AM OPTION value=PM PM /SELECT nbsp;nbsp; input type=submit /form ?php $testtime2=$hh2.:.$mm2. .$ampm2; echo Testtime = $testtime2brbr; $time_as_timestamp2=strtotime ($testtime2); echo Variable \testtime_as_timestamp2\ = $time_as_timestamp2brbr; $time_as_string2=strftime(%I:%M %p, $time_as_timestamp2); echo Variable \testtime_as_string2\ = $time_as_string2; ? /html Hi, All-- Has anyone already solved the problem of how to get a time input from the user in people time (12-hour clock) and format it to be INSERTed in a mySQL time field? The mySQL time field is 24-hour and of course it can be formatted in the SELECT statement to be displayed as 12-hour for the user. But--presumably in PHP--I need the user to be able to input a time, then validate it as to whether it is complete (e.g., expresses AM or PM and evaluates correctly), then pass it into an INSERT statement into the time field of a database. I suspect this already exists, just can't find an example. TIA Dave Shugarts -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL editor
Thus, the website to find Navicat is http://www.mysqlstudio.com. --Dave Shugarts Sorry, I mistyped. Navicat was previously known as MySQL Studio. Both are very nice packages. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Becoming Digital [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 19 June, 2003 11:02 Subject: Re: [PHP-DB] MySQL editor mysqlmanager (www.ems-hitech.com) is by far the best I've used. They've changed the name to Navicat now. It really is fantastic software. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Michael Scappa [EMAIL PROTECTED] To: Angelo Zanetti [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, 19 June, 2003 10:33 Subject: Re: [PHP-DB] MySQL editor Not free, but mysqlmanager (www.ems-hitech.com) is by far the best I've used. A free alternative (although not quite as featured) is probably Mysqlcc -Mike - Original Message - From: Angelo Zanetti [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 4:40 AM Subject: [PHP-DB] MySQL editor Hi all. I need to get a program (interface) for MySQL, I was using MySQL Front but they've discontinued its use. Any suggestions. thanx in advance -Angelo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Two-column display of data, second method
When I went looking for a script that would give me a two-column layout that
would list my faculty members in two roughly equal columns, alphabetized
down the first column and then the second, I did not find such a script.
[There was indeed a two-column script, but it fed the data row-by-row.]
I wrote this one and am glad to share it. The math statements could surely
be condensed, but I was using them to confirm the results. This script
either creates two equal columns if the total number of items is even, or it
makes the first column the longer if the total number of items is odd.
--Dave Shugarts
?php
/* *** Now selects the Faculty names ** */
$sql =SELECT FirstName, Middle, LastName
FROM $table_name
ORDER BY LastName, FirstName;
/* ** Now passes the result of the search ** */
$faculty_result = @mysql_query($sql, $connection) or die(Error #.
mysql_errno() . : . mysql_error());
$faculty_found = @mysql_num_rows($faculty_result);
$faculty_half = $faculty_found / 2;
$faculty_round = round($faculty_found / 2);
$faculty_remain = $faculty_found - $faculty_round;
echo table border=0
trtd colspan=2 align=centerb
Two-Column header
/bbr/td/tr
trtd width=49%\n;
for ($rownum = 1; $rownum = $faculty_round; $rownum++)
{
$row = mysql_fetch_array($faculty_result);
$FirstName=$row['FirstName'];
$Middle=$row['Middle'];
$LastName=$row['LastName'];
$faculty_block =
font class=facultydoc
$FirstName $Middle $LastName
/font
br
;
echo $faculty_blockbr;
}
echo /tdtd\n;
for ($rownum = 1; $rownum = $faculty_remain; $rownum++)
{
$row = mysql_fetch_array($faculty_result);
$FirstName=$row['FirstName'];
$Middle=$row['Middle'];
$LastName=$row['LastName'];
$faculty_block =
font class=facultydoc
$FirstName $Middle $LastName
/font
br
;
echo $faculty_blockbr;
}
echo /td/trbr/table;
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Time input formatting in PHP-12 hour clock
Hi, All-- Has anyone already solved the problem of how to get a time input from the user in people time (12-hour clock) and format it to be INSERTed in a mySQL time field? The mySQL time field is 24-hour and of course it can be formatted in the SELECT statement to be displayed as 12-hour for the user. But--presumably in PHP--I need the user to be able to input a time, then validate it as to whether it is complete (e.g., expresses AM or PM and evaluates correctly), then pass it into an INSERT statement into the time field of a database. I suspect this already exists, just can't find an example. TIA Dave Shugarts -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Now, how about Roman Numerals?
Hi, Hugh--
I am very grateful for your help and very impressed with this, but I am not
sure that I am able to confirm that it works and I am not competent to fix
it if it does not. Perhaps you can tell where I am going wrong.
I stored your script as a separate php document, RomanDigit.php. Here is
what I tested it with:
?php
include RomanDigit.php¹;
$a=8;
$b=19;
$c=155;
$d=980;
$ar= IntToRoman($a);
$br= IntToRoman($b);
$cr= IntToRoman($c);
$dr= IntToRoman($d);
echo ³
a = $a and ar = $ar br
b = $b and br = $br br
c = $c and cr = $cr br
d = $d and dr = $dr br
²;
?
And here are the results:
a = 8 and ar = VIII
b = 19 and br = IX
c = 155 and cr = V
d = 980 and dr = LXXX
TIA--Dave Shugarts
function RomanDigit($dig, $one, $five, $ten) {
switch($dig) {
case 0:return ;
case 1:return $one;
case 2:return $one$one;
case 3:return $one$one$one;
case 4:return $one$five;
case 5:return $five;
case 6:return $five$one;
case 7:return $five$one$one;
case 8:return $five$one$one$one;
case 9:return $one$ten;
}
}
function IntToRoman($num) {
if (($num 1) || ($num 3999))
return(No corresponding Roman number!);
$m = $num / 1000;
$c = ($num % 1000) / 100;
$x = ($num % 100) / 10;
$i = $num % 10;
return(
RomanDigit($m, 'M', '', '')
.RomanDigit($c, 'C', 'D', 'M')
.RomanDigit($x, 'X', 'L', 'C')
.RomanDigit($i, 'I', 'V', 'X')
);
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Now, how about Roman Numerals?
Hi, Hugh--
Thanks for both scripts! They are absolutely what I needed for my relatively
simple case and they now work great.
While I was thrashing around in my search for a conversion script, I found a
number of items that were out there in the realm of Javascript and
postgresql, etc. They were inscrutable to me, but I believe that some of
them were coming to grips with the finer points of roman numeral conversion.
While I cannot be a good judge, it sure seemed to me that this fellow had
covered all bases:
http://www.onlineconversion.com/roman_numerals_advanced.htm
Hope this is of some contribution to the general good.
I am amazed now to think what certain database software that blithely
converts roman-int-roman is actually doing behind the scenes!
--Dave Shugarts
Richard Hutchins [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Here's a rundown of what the script is doing based on your input:
If you pass in the number 155, here are the calculations:
$m = $num / 1000; //$m will be equal to .155
$c = ($num % 1000) / 100; //$c will be equal to 1.55
$x = ($num % 100) / 10; //$x will be equal to 5.5
$i = $num % 10; //$i will be equal to 5
[snip]
Yes, that's exactly the problem... I assumed
integer-only input and casting. Here is a fixed
(tested) version:
?php
function RomanDigit($dig, $one, $five, $ten) {
switch($dig) {
case 0:return ;
case 1:return $one;
case 2:return $one$one;
case 3:return $one$one$one;
case 4:return $one$five;
case 5:return $five;
case 6:return $five$one;
case 7:return $five$one$one;
case 8:return $five$one$one$one;
case 9:return $one$ten;
}
}
function IntToRoman($num) {
$num = (int) $num;
if (($num 1) || ($num 3999))
return(No corresponding Roman number!);
$m = (int) ($num * 0.001); $num -= $m*1000;
$c = (int) ($num * 0.01); $num -= $c*100;
$x = (int) ($num * 0.1);$num -= $x*10;
$i = (int) ($num);
// echo m = $m, c = $c, x = $x, i = $i ;
return(
RomanDigit($m, 'M', '', '')
. RomanDigit($c, 'C', 'D', 'M')
. RomanDigit($x, 'X', 'L', 'C')
. RomanDigit($i, 'I', 'V', 'X')
);
}
?
and my test script:
?php
include(to_roman.php);
$test = array( 8, 19, 155, 980, 9.8, -3, 3999, 4000, abc, , array() );
foreach($test as $num)
echo $num =gt; .IntToRoman($num).br/;
?
--
Hugh Bothwell [EMAIL PROTECTED] Kingston ON Canada
v3.1 GCS/E/AT d- s+: a- C+++ L+$ P+ E- W+++$ N++ K? w++ M PS+
PE++ Y+ PGP+ t-- 5++ !X R+ tv b DI+++ D-(++) G+ e(++) h-- r- y+
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Date Formatting in PHP
I have a simple need to reformat a variable coming in as $DatePick, brought forward from a MySQL select in the format: 2003-11-18 I need to convert it to the format November 18, 2003 I am trying to avoid having to mess with the MySQL select statement, as the output is being passed through several documents. So I need to do it within PHP. I tried making a conversion like $DayReport = date (F d, Y, $DatePick); But the value of $DayReport is neither correct nor does it change when $DatePick changes. For instance, in this example, the $DatePick value of 2003-11-18 gets converted to December 31, 1969. TIA Dave Shugarts -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date Formatting in PHP
This worked perfectly, is very simple for me to use, and I would never have
come upon it in 10,000 years of trying. Thanks!
Thanks also to everyone who offered ideas.
$f_date = date('F d, Y',strtotime($db_date));
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Now, how about Roman Numerals?
Second poser today: How to use either a MySQL select statement or PHP to produce a roman numeral, starting from an arabic (INT) number in a database? TIA Dave Shugarts -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] - Shopping cart software
A lot of hosted servers are already set up for Miva Merchant. The basic store/shopping cart is very quick to set up. --Dave Shugarts Can anyone recommend shopping cart software; does not have to be open source. I need to set up pretty fast and be able to calculate the different sales tax for anywhere, USA. (International should be ok). Already have cc processor lined up. Any suggestions would be appreciated. Thx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
