Re: [PHP-DB] Placing a form on a page
On Sun, May 5, 2013 at 11:22 PM, Ethan Rosenberg, PhD
erosenb...@hygeiabiomedical.com wrote:
Dear List -
I apologize if I offended some of you. I will see that it does not repeat.
I am trying to center a form on the top of a page. Here is the form:
What you're asking about has nothing to do with PHP.
You want help with HTML and CSS, and front-end design.
div id=frm
form name=poster method=post action=
input type=text name=cnum hidden=hidden /input
input type='hidden' name='welcome_already_seen' value='already_seen'
?php
echo centerinput type='submit' value='Validate Choice' //center;
echo /form;
?
/div
It is aligned on the p;age with the following css -
#frm{
position: fixed;
top: 5%;
left:42%;
}
It aligns perfectly with this form
function show_welcome()
{
$custnum = isset($_REQUEST[Cust_Num]) ? $_REQUEST[Cust_Num] : ;
$first_name = isset($_REQUEST[Fname]) ? $_REQUEST[Fname] : ;
$last_name = isset($_REQUEST[Lname]) ? $_REQUEST[Lname] : ;
$street = isset($_REQUEST[Street]) ? $_REQUEST[Street] : ;
$city = isset($_REQUEST['City']) ? $_REQUEST['City'] : ;
$state = isset($_REQUEST['State']) ? $_REQUEST['State'] : ;
$zip = isset($_REQUEST['Zip']) ? $_REQUEST['Zip'] : ;
$phone = isset($_REQUEST[Phone]) ? $_REQUEST[Phone] : ;
$date = isset($_REQUEST['Date']) ? $_REQUEST['Date'] : ;
$notes = isset($_REQUEST['Notes']) ? $_REQUEST['Notes'] : ;
$p1 = isset($_REQUEST['PH1']) ? $_REQUEST['PH1'] : ;
$p2 = isset($_REQUEST['PH2']) ? $_REQUEST['PH2'] : ;
$p3 = isset($_REQUEST['PH3']) ? $_REQUEST['PH3'] : ;
echo br /br /;
echo center;
echo form method=\post\;
echo strongCustomer Number:/strong input type='text' name='Cust_Num'
value='$custnum' //inputbr /br /;
echo strongFirst Name:/strong input type=\text\ name=\Fname\
value='$first_name' /;
echo strongLast Name:/strong input type=\text\ name=\Lname\
value='$last_name' //inputbr /br /;
echo strongStreet: /stronginput type= 'text' name= 'Street'
value='$street' //input;
echo strongCity: /stronginput type= 'text' name= 'City'
value='$city' //input;
echo strongState:/strong input type= 'text' size='2' maxlength='2'
name= 'state' value='NY' //inputnbsp;
echo strongZip Code:/strong input type= 'text' size='5' maxlength='5'
name= 'Zip' value='10952' //inputbr /br /;
echo /center;
echo center;
echo div id='phn' ;
echo strongPhone:/strong (input type='text' size='3' maxlength='3'
name='PH1' value='$p1')/input;
echo input type='text' size='3' maxlength='3' name='PH2' value =
'$p2'/input;
echo input type='text' size='4' maxlength='4' name='PH3' value =
'$p3'/inputbr /br /;
echo strongDate:/stronginput type='text' size='10' maxlength='10'
name='Date' value='$date' /inputbr /br /;
echo /center;
// echo /div;
echo center;
echo input type='submit' /br /br /;
echo nbspnbspnbspinput type='reset' value = 'Clear Form' /;
echo input type='hidden' name='welcome_already_seen'
value='already_seen';
echo input type='hidden' name='next_step' value='step2' /;
echo /center;
// goto step2;
?
/form
With this it is offset to the right.
echo centerstrongDo you wish to enter a Payment?/strong/center;
echo form name='form2' method='post';
echo centerstrongName: $_SESSION[Lname] echo $_SESSION[Fname]
/strong/center;
echo centerinput type='submit' name='button' value='YES' /center;
echo input type='hidden' name='welcome_already_seen'
value='already_seen';
echo input type='hidden' name='next_step' value='step5' /;
echo /form;
echo centerstrongDo you wish to enter a Charge?/strong/center;
echo form name='form1' method='post' ;
echo centerstrongName: $_SESSION[Lname],
$_SESSION[Fname]/strong/center;
echo centerinput type='submit' name='button' value='YES'
onclick='ClearScren' /center;
echo input type='hidden' name='welcome_already_seen'
value='already_seen';
echo input type='hidden' name='next_step' value='step6' /;
echo /form;
echo centerstrongName: $_SESSION[Lname],
$_SESSION[Fname]/strong/center;
echo centerstrongAre you Finished for Now?/strong/center;
echo form name='form1' action='exiter3.php' method='post' ;
// echo centerinput type='submit' name='button' value='YES' /center;
echo /form;
I know that I am doing something wrong, but cannot figure out what it is.
Ethan
PS I know that the formatting of the above is an absolute disaster. Icedove
defaults to 80 columns, and pure test does not allow me to change font size.
If you could show me how, it would be appreciated.
Thanks
--
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
Re: [PHP-DB] Undefined variables in update query
On Tue, Mar 26, 2013 at 10:57 AM, VanderHart, Robert
robert.vanderh...@umassmed.edu wrote:
I appreciate the replies I've received already; thanks! Sorry for not
catching my simple errors before sending out that message.
No worries, no one I have met yet begins life knowing this stuff!
If you don't mind, Robert, I'd like to introduce you to a couple of
concepts that might make your way easier:
1) Don't Repeat Yourself, aka DRY -- in writing code, it sometimes
seems easier to just cut and paste something over and over again with
a couple of minor tweaks. However, if you find you have to change
something structurally (like that form!), having the change in one
place is so much simpler.
2) Think in functions, procedures, and modules. It's sometimes very
easy when starting out to just write everything in one straight,
linear flow. Going along with #1, using functions to handle the
reduced repetition should help a lot.
If I can give a simple example, you have a whole swath of lines
dealing with pulling things out of $_POST that look almost exactly the
same, save for the key.
$fields = ['authorid','lname','fname','mname','email','institution',
'department','comments','sent_email','suffix']
foreach ($fields as $field) {
if (isset($_POST[$field])) $$field =
mysqli_real_escape_string($link, $_POST[$field]);
}
If you notice the '$$field' thing in the middle there, it's a Variable
Variable, which is described in:
http://www.php.net/manual/en/language.variables.variable.php
It might look advanced, but most every language has some form of
indirection like that. Makes the above operation really nice and
short. You said you've been around the block a couple of times in
other languages and scripts (and if your photo is accurate, we're
probably of an age!) so I hope you'll take this as just trying to help
a person get a leg up.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query will not work - SOLVED
On Sat, Feb 2, 2013 at 8:08 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: I must be missing something fundamental!! Yes. I was using the worker@localhost, which did not have the Update privilege. Added the privilege that, and everything worked. Please explain why you are not checking error returns from function calls? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running - Mystery SOLVED
On Tue, Jan 8, 2013 at 1:12 AM, Matt Pelmear mjpelm...@gmail.com wrote: I personally *never* set the error reporting level in the code, and during code reviews I reject code that does so. It is much cleaner to manage this in php.ini. I tend to agree -- settings like this are environment (dev, test, stage, prod) dpendent and generally the code being developed should work in all of them without modification. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Error Checking
On Tue, Jan 8, 2013 at 11:54 AM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: Dear List - 1] What function(s) do you use for error checking? 2] What are the settings for the parameter(s) in the functions? 3] Do you have different php.ini files for development and production? This makes more sense answered in reverse: 3. Yes, I have different php.ini files for each of my environments: development, testing, staging, and production. Generally speaking, my development and test environments run on the same box, my local workstation. Sometimes, test is run in a VM on my local workstation, but is entirely self-contained. Staging and production are generally on my VPS, under different directories and subdomains. 2. This is what I set in the appropriate php.ini for the environment: For dev and test: error_reporting = E_ALL | E_STRICT display_errors = On display_startup_errors = On html_errors = On log_errors = On For staging and production: error_reporting = E_ALL ~E_STRICT display_errors = Off display_startup_errors = Off html_errors = Off log_errors = On 1. Functions I use for reporting errors: error_log() to indicate internal application problems the code can detect. a home-brewed debugging function that provides a single call for insertion in code that will provide additional debugging information based on environment and configuration. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On Sun, Jan 6, 2013 at 1:21 AM, Karl DeSaulniers k...@designdrumm.com wrote: I likey.. :) Thanks! Best, Karl On Jan 5, 2013, at 10:05 PM, Bastien wrote: Get net beans. It's free and a great IDE with debuggers and other stuff Bastien Koert netbeans is pretty awesome; I haven't used it for PHP, though. the PHP verison of Eclipse you can snag from Zend for free is okay, but creaky slow on low-memory system. Sublime Text 2 is getting a lot of love these days, although not a fully-fledged IDE, contains oodles of stuff to help develop software. I've never used GoLive, so really can't even compare. Emacs is still my go-to editor, but that's mostly because it's the one i imprinted on decades ago. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On Sun, Jan 6, 2013 at 2:14 PM, Karl DeSaulniers k...@designdrumm.com wrote: Unfortunately, I am still working on an old G4 Powerbook trying to finish a website of mine. Adobe GoLive was the program I imprinted on and it stuck. Sadly it has gone away now and Dreamweaver is the replacement. I am not fond of DW but will no doubt have to adjust to get a job in the industry as it is the main stay these days for HTML. Sadly DW isn't any better (that I know of) when it comes to editing, debugging and creating server-side tech. So this NetBeans thingamajig is a real pleaser for me... :) Actually, you might find netbeans just all that for writing the HTML, CSS and JS stuff, too. I used Dreamweaver when it first came out and compared to the other sorts of things available at the time, it was pretty spiffy. But time has marched on, and creating HTML isn't something I want a visual editor for. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Programs not Running - SOLVED
On Sun, Jan 6, 2013 at 6:32 PM, Jim Giner jim.gi...@albanyhandball.com wrote:
On 1/6/2013 7:17 PM, Ethan Rosenberg, PhD wrote:
The problem was in this piece of code:
require '/var/www/pass.inc';
$db = Store;
$cxn = mysqli_connect($host,$user,$password,$db);
if ( !$cxn ) {
die( 'connect error: '.mysqli_connect_error() );
}
I had moved the password file [/var/www/pass.inc] out of root, but
forgot to change the code!
This still leaves a question:
if ( !$cxn ) {
die( 'connect error: '.mysqli_connect_error() );
}
You're asking why the die was reached rather than having a fatal error occur
because the require couldn't be performed? Perhaps because there exists
another pass.inc file somewhere in PHP's default search folders for the
require, but it contained the wrong password?
It is a bit difficult having not seen a shred of Ethan's scripts'
actual output, and questionable actual content, but the snippet as
shown here is an absolute path specification, and thus php would not
go through it's load path to look for it:
?php
$testpath = /tmp/www;
$ipath = ini_get(include_path);
$ipaths = explode(:,$ipath);
if (!file_exists($ipaths[0].$testpath)) {
mkdir($ipaths[0].$testpath,0777,true);
}
file_put_contents($ipaths[0].$testpath./testfile,This is the
searched version);
require($testpath./testfile); // note that the absolute path is given here
-*- mode: compilation; default-directory: /home/tamara/Projects/examples/ -*-
Compilation started at Sun Jan 6 21:27:03
php phpincludepath.php
PHP Warning: require(/tmp/www/testfile): failed to open stream: No
such file or directory in
/home/tamara/Projects/examples/phpincludepath.php on line 13
PHP Stack trace:
PHP 1. {main}() /home/tamara/Projects/examples/phpincludepath.php:0
PHP Fatal error: require(): Failed opening required
'/tmp/www/testfile' (include_path='.:/usr/share/php:/usr/share/pear')
in /home/tamara/Projects/examples/phpincludepath.php on line 13
PHP Stack trace:
PHP 1. {main}() /home/tamara/Projects/examples/phpincludepath.php:0
Compilation exited abnormally with code 255 at Sun Jan 6 21:27:03
Thus, I would conclude that the file /var/www/pass.inc actually *did*
previously exist, but contained incorrect credentials, making the
require not complain, but the connect complain instead.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On Sat, Jan 5, 2013 at 12:48 AM, Karl DeSaulniers k...@designdrumm.com wrote: On Jan 4, 2013, at 8:00 PM, tamouse mailing lists wrote: On Fri, Jan 4, 2013 at 6:09 PM, Matt Pelmear mjpelm...@gmail.com wrote: On 01/04/2013 01:40 PM, tamouse mailing lists wrote: One should be able to solve parsing problems without resorting to using a web server. Every time I save a php file, I have my editor set to run it through php -l. Saves lots of angst when I get to the web testing stage. This is a good idea in general. It's also a good trick to check for this problem from the command line when suspecting a parse problem. Most IDEs will make parse errors very obvious while you are editing the file, too. Unfortunately not all members of any given development team can be relied upon to test things in this way before committing ;) Committing a syntax error which breaks integration is a HUGE HUGE NO NO. Such a person would likely be cast from a dev team, or relegated to some more benign task. For those of us who have no idea of what you speak, can you elaborate so as we don't make that mistake? Or are you just talking about submitting a file to production without testing it? Not necessarily directly to production, but the beginning of the stream that will take something from development, through testing, staging, and finally into production. The initial point where you merge things is generally shared by all developers on a project, and to break it means you are breaking things for everyone on your team. Note this applies to mulitperson projects; if you do this to yourself on your solo project, not so much of a problem. I personally have not used command line. It is new to me, but sounds integral. Where can one study its use? You can start with: http://php.net/manual/en/features.commandline.php As for telling your editor to pass the file through php -l, that's something that is extremely editor specific. I can tell how it's done in Emacs, TextMate and Sublime Text 2, but naught else. Using a full-fledged IDE such as Eclipse or PHPStorm will automatigically flag syntax problems. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On Fri, Jan 4, 2013 at 12:37 AM, Matt Pelmear mjpelm...@gmail.com wrote: In my experience, the problem you are experiencing is caused by one of two issues: 1) A major parsing problem (sometimes no error is displayed-- you just get no output.) One should be able to solve parsing problems without resorting to using a web server. Every time I save a php file, I have my editor set to run it through php -l. Saves lots of angst when I get to the web testing stage. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Programs not running
On Fri, Jan 4, 2013 at 6:09 PM, Matt Pelmear mjpelm...@gmail.com wrote: On 01/04/2013 01:40 PM, tamouse mailing lists wrote: One should be able to solve parsing problems without resorting to using a web server. Every time I save a php file, I have my editor set to run it through php -l. Saves lots of angst when I get to the web testing stage. This is a good idea in general. It's also a good trick to check for this problem from the command line when suspecting a parse problem. Most IDEs will make parse errors very obvious while you are editing the file, too. Unfortunately not all members of any given development team can be relied upon to test things in this way before committing ;) Committing a syntax error which breaks integration is a HUGE HUGE NO NO. Such a person would likely be cast from a dev team, or relegated to some more benign task. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Shopping Cart Discount System
On Mon, Dec 31, 2012 at 2:31 AM, Karl DeSaulniers k...@designdrumm.com wrote: Hello Everyone, Hope all are well. Quick and hopefully painless question. Is there any examples on how to build a discount system into your shopping cart out there that anyone knows of? I am using MySQL and PHP. I have built one of my own so far, but am having trouble making sense of what goes where. For example. If a product has miscellaneous charges, lets say.. glitter is extra on your shirt. How is discounts applied? I mean, as far as discounts go, lets say its 20% a shirt and this shirt has glitter. Is it best practice to apply the discount to just the shirt or the shirt and glitter as a combo discount. I know this is somewhat dependent on the owners choice of what he/she wants to give the discount on, but my question is of the programing of it. Do I build conditions for the shirt to get a discount applied then the miscellaneous charges, or combine the totals of the two, then apply the discount to the sum? Then lets say there is a cart discount also being applied. Is it best practice to apply this to the total of items then add the shipping, rush charges and tax, or to the total of the whole cart including shipping, rush charges then add the tax after applying the discount? These sound much more like business operating questions than programming questions, per se. I think it really depends a lot on what other businesses in the same niche as yours do, or if you can find out. Once you have figured out the business practices, coding them should be easier, I'd say, but doing it the other way 'round seems like would lead to confusion. Some questions from a really non-business person: 1. Why are you offering discounts at all? What do you hope to accomplish by offering discounts? 2. Who qualifies for which discounts and why? 3. Do discounts apply to just the base merchandise, or the whole end piece, with whatever customisations it might have, and if so or if not, why? 4. Are there different discount rates applied to base merchandise and subsequent customisations, and if so, why? 5. Is there any reason to make this complicated, or instead just offer a flat rate discount on final price, before taxes and SH? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements - Search
On Mon, Dec 3, 2012 at 3:34 PM, Jim Giner jim.gi...@albanyhandball.com wrote: On 12/3/2012 2:30 PM, Ethan Rosenberg, PhD wrote: $stmt = mysqli_stmt_init($cxn); mysqli_stmt_prepare( $stmt, $sql12 ); The search fails. So, you got us down to where you prepare the sql statement in your code. But where do you actually perform the bind, execute it, and then bind the results? Hard to say anything about what's happening yet. Here's thing. Skip all the constructing of intput. Skip all the formatting code for now. Your *first* step is to make your queries work. *Then* you can make it pretty. Make sure your queries work, your results from the queries are what you want, just var_dump them for now. Take things one step at a time. I'm wondering how many times we're going to see the *SAME CODE* over and over as you progress one painful step at a time and never figure anything out for yourself because you're trying to do too much at once. The things people told you change last time are still in this code. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysqli_bind_result
On Mon, Nov 26, 2012 at 7:28 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: Dear List - Here is some code: $sql13 = SELECT * FROM Intake3 WHERE MedRec = ?; $stmt = mysqli_stmt_init($cxn); mysqli_stmt_prepare( $stmt, $sql13 ); $_SESSION['stmt'] = $stmt; $args = array(); $args[0] = $_POST['MR']; $types = 'i'; if( !$stmt ) throw new Exception( 'Error preparing statement' ); // Put the statement and types variables at the front of the params to pass to mysqli_stmt_bind_param() array_unshift($args, $stmt, $types ); // Note that I've moved this call. Apparently it doesn't pass back the result. //I guess sometimes I just forget these things. // mysqli_stmt_bind_param() if( !call_user_func_array( 'mysqli_stmt_bind_param', $args ) ) throw new Exception( 'Failed calling mysqli_stmt_bind_param' ); if( !mysqli_stmt_execute( $stmt ) ) throw new Exception( 'Error while executing statement' ); mysqli_stmt_bind_result( $stmt, $Site, $MedRec, $Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age); throw new Exception( 'Failed calling mysqli_stmt_bind_result' ); Fatal error: Uncaught exception 'Exception' with message 'Failed calling mysqli_stmt_bind_result' What is my error?? FYI - MR comes from here -- centerstrongDo you Wish to Enter Visit Data?/center centerIf Yes:/center form name=form2 method=post centerChoose the Medical Record that You Wish to Use/strong/center form method=post centerinput type=text name=MR/input/center centerinput type=submit value=Enter Medical Record//center input type=hidden name=next_step value=step10 / /form Advice and help, please. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php From the looks of this, you are calling the throw all the time: mysqli_stmt_bind_result( $stmt, $Site, $MedRec, $Fname, $Lname, $Phone, $Height, $Sex, $Hx, $Bday, $Age); throw new Exception( 'Failed calling mysqli_stmt_bind_result' ); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] CSV storage InnoDB?
On Mon, Nov 26, 2012 at 8:10 PM, Karl DeSaulniers k...@designdrumm.com wrote: On Nov 26, 2012, at 8:01 PM, Karl DeSaulniers wrote: On Nov 26, 2012, at 7:29 PM, Bastien wrote: Bastien Koert On 2012-11-26, at 7:43 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello all, Quick question. What is the best way to store a comma separated list of discount codes in a InnoDB? Text, varchar, blob, ? I want to be able to store discount codes offered to users in their profile, in their cart, etc. So I am thinking of storing the codes as a comma separated list that I can then refer to, add to or remove codes from. The codes will change every once and a while and they list of codes may be rather large after say a year, so I am shying away from an enum() or anything like that. Any help/pointers is greatly appreciated. Best, Karl -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl. Is there a way to group these codes? Can profiles be the same for different users? If so another table for the code and a join table for the users to codes might be simpler to manage Bastien Hi Bastien, Thanks for your reply. Sorry, I should have elaborated. There is a table that holds all the discount codes. Keeps them grouped with a product id or if they are to be applied cart wide. So the field I am asking for will hold codes that are being used by either a product or by a user or if it is a cart discount. Each have their own tables, the products, the users and the cart. In these tables is where I need to insert this field. An example of the discount table would be like: Discount table: | discount code | product id | discount perc. | discount type | +-+-+--+---+ |8lKzv_=== | 22 | 20 | item | +-+-+--+---+ | NpLK23 | 0 | 50 | cart | +-+-+--+---+ but in the user table for instance: User table: | user_discounts | +---+ |8lKzv_===, YjMlio_===, NpLK23, hDflp0_=| +---+ Just trying to figure out the best way to store multiple variables if you will in one field like so. Or if this is even the best way to do such a thing. I am open to alternatives. I will need to read this new filed out of the database as an array that I can then utilize with an in_array or something of the sort. There may be a best practices on this that I am not aware of with my somewhat limited PHP exp. Hope that makes more sense. TIA. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I think I may have asked this already a year or so ago, but cannot find my old emails. Sorry for any redundancy. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Hi, Karl, Your idea can work -- storing the list of discount codes as comma delimited (or some known delimiter that won't show up in the codes) is pretty easy, and when you fetch it out you can explode it into an array, and use in_array to check. (Something you might want to consider is storing it as a JSON string as well.) There's also the right way to do this, for some value of right, which is the many-to-many relationship using a foreign key table. This can be painful if you're not really strong on SQL, especially dealing with updates and deletes. (Example: if you delete a discount code, you need to delete the associated records in the foreign key table.) Generally this sort of operation is best done as stored procedures, rather than multiple queries from PHP. Question: do the discount codes only apply to one product, or can a discount code be used for several products? If the latter, then you have the same sort of issue of a many-to-many relationship, requiring the foreign key table there... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Formatting
On Sun, Nov 25, 2012 at 4:40 PM, Karl DeSaulniers k...@designdrumm.com wrote: Your also using a combination of th and td. I am thinking that you have columns and that they lay next to each other. Lose the th and just use td Hmm, well, no, it looks like the table header columns are in fact just table header columns, and table data columns are just table data columns. These are structurally different things; th and td mean different things, appropriately. th may be rendered different than td when there are no style sheets, or by tty devices and screen readers, etc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Debugger
On Wed, Oct 24, 2012 at 7:20 PM, Christopher Jones christopher.jo...@oracle.com wrote: On 10/24/2012 05:02 PM, Ethan Rosenberg, PhD wrote: Dear list - A. Is anyone aware of a debugger that: 1] will step thru the code, 2] will stop at a point that input is requested [eg, form] and allow input? B. Aptana gives the following message: 'Launching Firefox' internal server has failed. Server configuration null not found. Advice and help, please. Ethan Look at NetBeans with XDebug: http://netbeans.org/kb/trails/php.html http://netbeans.org/kb/docs/php/debugging.html Chris -- christopher.jo...@oracle.com http://twitter.com/#!/ghrd -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This just floated by from smashing magazine's recently newsletter. Haven't looked at it at all, but you can research it: 3. Faster And Better PHP Debugging Debugging code, be it your own or someone else's, can be terribly time-consuming. The error messages are often quite ambiguous or not transparent enough, and it takes hours to find the tiny string of code that just doesn't do what it's supposed to. Joseph Lenton's little tool may be just what you need to speed up the process. With PHP Error, the appearance of an error triggers a page with a full stack trace, with the erroneous syntax highlighted. To use the tool, just include the library in your project and customize it to meet your requirements and needs. The library allows you to catch Ajax errors, find cases when a class is loaded but doesn't exist or supress errors. A detailed documentation and setup guide are provided as well. The code is provided on GitHub. (jc) http://phperror.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need some opinions on solution
On Wed, Oct 17, 2012 at 10:53 PM, Bastien Koert phps...@gmail.com wrote: Hi All, Below is a situation I ran into recently. To me, the decision made shows how to not solve a problem but merely compound the issue. Scenario: There is an app currently running on a highly loaded 4.11 MySQL db. Customers are complaining about the slow performance. The app has 3 important features (configurable forms created in userland, confgurable reporting engine and the ability to add/create an unlimited number of fields). The app does this by having a 200 field main table running in the normal fashion and a smaller table that holds the userland created fields that essentially runs vertically. It also supports MSSQL as well Under MySQL 4.11 the join between the two tables is problematic in that queries must pivot the small table to make it match the large table. There are also a number of MySQL 5+ servers available. The solutions: There are three solutions, one mine and two from a manager. Management Solution 1. Create another 500 column wide table and transfer the data from the smaller vertical table to the large new table Issues: 1. This kills the add unlimited fields feature (which makes a lot of sales happen) but improves performance 2. The DBA in me screams about a 500 column table as a really bad idea since 99% of the fields will be null 3. Requires another code base (already too many) Management Solution 2. Create 50 additional fields on the main 200 field table to move the most used fields from the vertical table and improve performance Issues: 1. Doesn't solve the additional vertical fields problem, just delays the performance slow down for a while 2. Required another code base ( the DEV in me hates this, there are too many already) 3. Only solves the problem for ONE client (the PM in me hates this since its about a months work to analyze and code) 4. Creates a non standard app database (the DBA in me hates this) My Solution: Move the DB to MySQL 5, re-code the report engine to create views against those tables without altering the db structure Issues: 1. slightly more complex code 2. time to migrate and test system The Pros: 1. The solution can be used across both MySQL and MSSQL with some minor syntactic sugar 2. Standard DB for app 3. Simpler to code and doesn't require major analysis to do 4. Keeps everything in one codebase Now its obvious which way I lean, but mgmt decided on solution 2. I'd like some comments to see what you guys think. -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php As soon as you started talking about the structure, my first thought was views, so yeah, that's where'd I'd have gone. Then the subversive in me whispered MongoDB... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Program Dies
On Mon, Oct 15, 2012 at 11:14 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 10/14/2012 5:57 PM, Ethan Rosenberg, PhD wrote: Dear List - Thank you ever so much for all your help. I apologize in advance for dumping all this code on you. I cannot get any debugger to work for me [see my separate email on debuggers]. I placed numerous echo and print_r statements in the code, and could not find the error. The program worked perfectly previously, and now it dies. It mimic a doctor's office where a study is being performed on obesity. The parameter is a calculated value called BMI. In the initial part of the program, a search is performed to obtain the medical record . This works perfectly. The second step is to obtain data at the patient visit, which is triggered by a button Do you wish to enter visit data? If that button is clicked, the program returns to the original welcome screen. Advice and help, please. Here is the code: Ethan - If this is the code you are referring to, then I have some questions about it - some of them pertinent, some of them impertinent. 1 - do you plan on putting all of your input tags in their own div tags? Kinda needless imho. 2 - if this is the form holding the button that you referred to in your post, why is there no action specified? I've never tried code like that so I don't even know where one would end up in such a case. Lastly - Why the center/center coding? If you are going to show us what you are doing - at least have the sense to clean up the this kind of silly stuff to show us that you know what you are doing. centerDo you Wish to Enter Visit Data? /center form method=post action= div id=vsinput type=radio name=decision value=1 checked/Yesinput type=radio name=decision value=0 /No/divbr /br / center/centerinput type=submit value=Enter Visit Data //center -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php form with an empty action= attribute simply calls the same URL as given. I use this all the time for testing and for localhost one-page apps, but is a Very Bad Idea ™ for a real on-the-internet application. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Program Dies
On Tue, Oct 16, 2012 at 9:53 PM, Ethan Rosenberg, PhD
erosenb...@hygeiabiomedical.com wrote:
snip
form with an empty action= attribute simply calls the same URL as given. I
use this all the time for testing and for localhost one-page apps, but is a
Very Bad Idea ™ for a real on-the-internet application.
Thank you for your input.
Please, teach me, why is it a Very Bad Idea ™
Ethan
Generally speaking, you want to be very sure of the routes,
parameters, and data that is going to be coming into your application
from a form (and really, anywhere, but form in this case).
If you leave action empty or omit it, it calls the URL back that is
shown to the browser. This isn't always a problem, but if the URL
happens to have additional information such as an info path and/or a
query string, the possibility of it being abused is increased, and
it's a potential vector for hacking your application.
In addition, the existence of things like path info and query strings
is a potential source of defects if you don't know it's happening and
you don't code around it.
Finally, although many many applications do this, it is generally
considered a poorer user experience to press the back button and get
a message saying that the page has expired and the browser needs to
resend data, as well as a possible application headache if you end up
processing the same information twice. Thus better to specifically set
the action to the script that will process the information, and give a
302 redirect in that script to whatever the response is via a location
redirect:
form.html:
form action=process.php method=POST
!-- rest of the form --
/form
process.php:
?php
/* give 302 redirect to browser */
header('Location: http://www.example.com/response.html');
/* process the form data */
?
response.html:
h1Thank you/h1
pThank you for submitting your information./p
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Vulnerability Announced in phpMyAdmin
On Tue, Sep 25, 2012 at 3:20 PM, Daniel Brown danbr...@php.net wrote: Just a three-list cross-post to bring it to everyone's attention at once, in case you weren't already aware. It was announced today that a compromised SourceForge mirror was distributing a malicious file with the phpMyAdmin package that allows an attacker to arbitrarily execute code on a server hosting the exploitable package. Obligatory (not intentionally self-serving) social media link here: https://twitter.com/oidk/status/250688002005811200 Signal boosting... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] php code for sending SMS
On Wed, Sep 26, 2012 at 1:18 AM, Dr Vijay Kumar vaibhavinformat...@gmail.com wrote: I wish to send a SMS to a mobile no. thru PHP/HTML. Any suggestions Please? Try an email - SMS gateway? http://en.wikipedia.org/wiki/List_of_SMS_gateways -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Why the sudden dis-interest?
Some of them may not end up being PHP + DB programmers? Or joined under mistaken expectations? Or thought it might be a good idea at the time and ended up doing something else? Lots of possible reasons. Given the inability of the folk to figure out *how* to unsubscribe, I'd suspect rather limited understanding of internet technology to begin with. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Unsubscribe me please
On Thu, Sep 20, 2012 at 3:37 AM, whe...@eircom.ie wrote: Me too (whe...@eircom.ie) *** The information contained in this e-mail and any files transmitted with it is confidential and may be subject to legal professional privilege. It is intended solely for the use of the addressee(s). If you are not the intended recipient of this e-mail, please note that any review, dissemination, disclosure, alteration, printing, copying or transmission of this e-mail and/or any file transmitted with it, is prohibited and may be unlawful. If you have received this e-mail by mistake, please promptly inform the sender by reply e-mail and delete the material. Whilst this e-mail message has been swept for the presence of computer viruses, eircom does not, except as required by law, represent, warrant and/or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, viruses, interception or interference. eircom Limited. Private Company Limited by Shares. Registered in Dublin. Registration Number 98789. Registered Office - 1 Heuston South Quarter, St. John’s Road, Dublin 8. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ok, that is just precious. 1 line of content. 29 lines of fluff. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Adding entry to /dev
On Mon, Sep 10, 2012 at 11:27 PM, Jim Giner jim.gi...@albanyhandball.com wrote: On 9/10/2012 7:41 PM, Ethan Rosenberg, PhD wrote: Dear list - How do I add a new entry to /dev; eg, /dev/sdb? Thanks, Ethan Rosenberg Ethan, Sometimes google is great at answering this kind of stuff. Did you try a search on your question. I did and found an answer in the fifth result returned. Search on php add a new subfolder Except on *nix, where /dev contains device files, which are not just run-of-the-mill files. This question has nothing to do with php (you should NOT be making device files with php!). /dev/sdb by it's name will typically refer to a block implementation of a SCSI disk, or a device which ends up using the SCSI drivers, such as USB drives. The various partitions on the device will then be represented by /dev/sdb1, /dev/sdb2, etc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
On Sun, Sep 2, 2012 at 10:24 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement What exactly is unclear about that? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
On Mon, Sep 3, 2012 at 7:45 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 9/3/2012 2:44 AM, tamouse mailing lists wrote: On Sun, Sep 2, 2012 at 10:24 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com wrote: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement What exactly is unclear about that? Actually - from looking at the code the OP posted, I don't see the mis-match either, assuming that the post contains the actual code. That would be a question, considering the OP code was incorrect as it stood. I do have a question tho. Not being familiar with mysqli yet (soon, I know), I'm wondering what his die clause is actually saying when it mentions: ... mysqli_stmt($stmt) Does the reference to 'mysqli_stmt' mean something special, since it doesn't reference any particular function? It seems like it should just give a Fatal error for a call to an undefined function, as mysqli_stmt itself is a class, with no constructor method of it's own, as it's created via the mysql_prepare(). I get a little confused when mixing OO and procedural versions, though, so it might do something... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Wow, this is weird
On Mon, Sep 3, 2012 at 7:58 PM, David McGlone da...@dmcentral.net wrote: Why was the brackets necessary? I thought that was what the single quotes were for. I'm thinking the brackets join ['image'] and ['name'] otherwise the query views it as 2 seperate queries. Correct? or were the brackets use to group it all together, but seperate it from the outer quotes? Reading through this should help: http://us.php.net/manual/en/language.types.string.php#language.types.string.parsing The part to pay attention to most is the section entitled Complex (curly) syntax. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Prepared Statements Insert Problem
if($stmt = mysqli_stmt_prepare($stmt, INSERT INTO Intake3 (Site, MedRec,
Fname, Lname, Phone, Height, Sex, Hx, Bday, Age)
VALUES(?,?,?,?,?,?,?,?,?,?)!=0)
Let me break this into smaller chunks:
if ($a = $b != 0)
Precedence rules show that comparisons (!= in this case) come before
assignment (=).
So, what you're doing is this adding parens:
if ($a = ($b != 0) )
when, in fact, you want this:
if ( ($a = $b) != 0)
which is syntactically equivalent to:
if ( ($a = $b) )
This is the first part of your problem.
The second part, is that you are assigning to $stmt, which is what you
pass in the mysqli_stmt_prepare function, thus, after you've prepared
the statement, you overwrite it with the return value of the function,
which in procedural context, is either TRUE or FALSE, thus destroying
the work you just performed.
What you need here is:
$sql = INSERT INTO Intake3
(Site, MedRec, Fname, Lname,
Phone, Height, Sex, Hx, Bday, Age)
VALUES (?,?,?,?,?,?,?,?,?,?);
if ( mysqli_stmt_prepare( $stmt, $sql ) )
{
// bind and process the stmt
}
else
{
die('Error occured during statement prepare: ' .
mysqli_stmt($stmt) );
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Wow, this is weird
On Tue, Aug 28, 2012 at 6:12 AM, David McGlone da...@dmcentral.net wrote:
On Tuesday, August 28, 2012 12:41:19 AM you wrote:
Here, in your original pastebin, at line 36:
mysql_query (INSERT INTO inventory(image, year, make, model, milage, price)
VALUES('$_POST[image]', '$_POST[year]', '$_POST[make]',
'$_POST[model]', '$_POST[milage]', '$_POST[price]'));
should be:
mysql_query (INSERT INTO inventory(image, year, make, model, milage, price)
VALUES('{$_FILES['image']['name']}', '$_POST[year]', '$_POST[make]',
'$_POST[model]', '$_POST[milage]', '$_POST[price]'));
This method was tried, and didn't work, it was inserting Array[name] into
the db. This method was also what made me realize that $_FILES['image']
['name'] is being interpreted as an array. So what I did was assigned the
value to a variable.
I think you must be missing the '{}' brackets, or something, because
with this added to the snippet from before:
$sql = INSERT INTO inventory(image, year)
VALUES('{$_FILES['image']['name']}', '$_POST[year]');
echo 'h2$sql = /h2pre'.PHP_EOL;
var_dump($sql);
echo '/pre'.PHP_EOL;
I get:
$sql =
string(58) INSERT INTO inventory(image, year) VALUES('1.png', '2012')
which shows $_FILES['image']['name'] correctly interpolated to a string.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Wow, this is weird
On Mon, Aug 27, 2012 at 8:03 PM, David McGlone da...@dmcentral.net wrote:
I got it. All I needed to do was change $_POST[image] to $image in my query
like so:
mysql_query (INSERT INTO inventory(image, year, make, model, milage, price)
VALUES('$image', '$_POST[year]', '$_POST[make]',
'$_POST[model]', '$_POST[milage]', '$_POST[price]'));
}
I'm sortof stumped as to why though. I'm still pondering it and probably will
all night. I'll probably wake up at 3am and the light bulb will go off in my
head.. LOL
I would check to see if you have somewhere set $image. I don't see it
in your code, but I'm sometimes pretty blind.
If you actually dump out $_POST from your form input, you will see
there is no 'image' entry -- that is because it is type file in your
form. When you dump $_FILES, of course, you see the image there.
Here's output from a trial I just made, with the following code:
?php
echo 'h2$_POST = /h2pre'.PHP_EOL;
var_dump($_POST);
echo '/pre'.PHP_EOL;
echo 'h2$_FILES = /h2pre'.PHP_EOL;
var_dump($_FILES);
echo '/pre'.PHP_EOL;
?
form enctype=multipart/form-data action= method=POST
input type=hidden name=MAX_FILE_SIZE value=10 /
Image: input name=image type=file /br /
Year: input type=text name=year size=40br /
input type=submit name=Submit value=Insertbr /
/form
Outputs:
$_POST =
array(3) {
[MAX_FILE_SIZE]=
string(6) 10
[year]=
string(4) 2008
[Submit]=
string(6) Insert
}
$_FILES =
array(1) {
[image]=
array(5) {
[name]=
string(5) 1.png
[type]=
string(9) image/png
[tmp_name]=
string(26) /private/var/tmp/phpeVMSM5
[error]=
int(0)
[size]=
int(37543)
}
}
You also don't need to use basename($_FILES['image']['name']) -- the
only thing stored there is the basename already.
Here, in your original pastebin, at line 36:
mysql_query (INSERT INTO inventory(image, year, make, model, milage, price)
VALUES('$_POST[image]', '$_POST[year]', '$_POST[make]',
'$_POST[model]', '$_POST[milage]', '$_POST[price]'));
should be:
mysql_query (INSERT INTO inventory(image, year, make, model, milage, price)
VALUES('{$_FILES['image']['name']}', '$_POST[year]', '$_POST[make]',
'$_POST[model]', '$_POST[milage]', '$_POST[price]'));
(I'm hoping what you are showing us is purely for learning sake, and
that you will also be learning to untaint your input.)
(Also, minor minor nit: it's spelled mileage :) )
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: No data?
Brad, do take a look at http://pastie.org/4340383 It does work. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: No data?
With one change to the data file to make the indexing work correctly
and read all the fields: to accommodate the first id auto_increment
field, the first value in the csv line must be null:
null.a,b,c,d,e
null,f,g,h,i,j
null,k,l,m,n,o
And further, the field count should subsequently be one less in line
53 when creating the table:
for ($i=0; $i (count($fields)-1); $i++) {
and that should do it.
On Thu, Jul 26, 2012 at 10:20 PM, tamouse mailing lists
tamouse.li...@gmail.com wrote:
Brad, do take a look at http://pastie.org/4340383
It does work.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: No data?
argh
forget that.
simply put the id auto_increment field *last* in the create table
defn, leave everything else as it was.
On Thu, Jul 26, 2012 at 10:38 PM, tamouse mailing lists
tamouse.li...@gmail.com wrote:
With one change to the data file to make the indexing work correctly
and read all the fields: to accommodate the first id auto_increment
field, the first value in the csv line must be null:
null.a,b,c,d,e
null,f,g,h,i,j
null,k,l,m,n,o
And further, the field count should subsequently be one less in line
53 when creating the table:
for ($i=0; $i (count($fields)-1); $i++) {
and that should do it.
On Thu, Jul 26, 2012 at 10:20 PM, tamouse mailing lists
tamouse.li...@gmail.com wrote:
Brad, do take a look at http://pastie.org/4340383
It does work.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Mon, Jun 25, 2012 at 7:57 AM, Peter Beckman beck...@angryox.com wrote: I'll save you some time, it's not the where 1 part of the query: mysql select count(*) from numbers where 3 and num like '1212%'; The only time the where clause fails (rightly so) with a single number like what is when you do where 0. mysql select count(*) from quotes where 0; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql select count(*) from quotes where 1; +--+ | count(*) | +--+ | 727 | +--+ 1 row in set (0.00 sec) Note that the where clause failing does NOT mean the select failed. It correctly returned 0 rows. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Mon, Jun 25, 2012 at 9:46 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Mon, Jun 25, 2012 at 7:57 AM, Peter Beckman beck...@angryox.com wrote: I'll save you some time, it's not the where 1 part of the query: mysql select count(*) from numbers where 3 and num like '1212%'; The only time the where clause fails (rightly so) with a single number like what is when you do where 0. mysql select count(*) from quotes where 0; +--+ | count(*) | +--+ | 0 | +--+ 1 row in set (0.00 sec) mysql select count(*) from quotes where 1; +--+ | count(*) | +--+ | 727 | +--+ 1 row in set (0.00 sec) Note that the where clause failing does NOT mean the select failed. It correctly returned 0 rows. Ooops, I mean *1* row with value zero. /o\ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 5:55 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 04:21 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. This is a suggestion I received from this list - + $query = select * from Intake3 where ; Maybe I missed it, but you need to have a 1 after the where part in your select. So... $query = SELECT * FROM Intake3 WHERE 1 ; I must stress that the query works from the terminal, and fails in the PHP code. My question is why? Ethan First off, all where 1 does is make the statement always true. If you don't believe that, try this query: SELECT * FROM Intake3 WHERE 1; in mysql and contrast with: SELECT * FROM Intake3; you should see identical results. Secondly, you need to look at where you are gathering the return from the query. You show: $result = mysqli_query($cxn,$query); but you aren't showing us what you do with $result, as in where you fetch the rows and process them. I'm dubious that the query is in fact only returning one record. But you can check by echoing mysqli_num_rows($result). (I think; I never use the procedural version, opting to use the object version instead.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 8:22 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Sun, Jun 17, 2012 at 5:55 PM, Ethan Rosenberg eth...@earthlink.net wrote: At 04:21 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... At 03:30 PM 6/17/2012, Jim Giner wrote: Ethan Rosenberg eth...@earthlink.net wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... Dear List - The same query in a PHP program will only give me results for MedRec 10003 why the where 1 clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think where 1 enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the 1 on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key 100 and a.key = b.key ORDER BY a.fld1 Im no expert, but hopefully this makes it a little less complex for you. This is a suggestion I received from this list - + $query = select * from Intake3 where ; Maybe I missed it, but you need to have a 1 after the where part in your select. So... $query = SELECT * FROM Intake3 WHERE 1 ; I must stress that the query works from the terminal, and fails in the PHP code. My question is why? Ethan First off, all where 1 does is make the statement always true. If you don't believe that, try this query: SELECT * FROM Intake3 WHERE 1; in mysql and contrast with: SELECT * FROM Intake3; you should see identical results. Secondly, you need to look at where you are gathering the return from the query. You show: $result = mysqli_query($cxn,$query); but you aren't showing us what you do with $result, as in where you fetch the rows and process them. I'm dubious that the query is in fact only returning one record. But you can check by echoing mysqli_num_rows($result). (I think; I never use the procedural version, opting to use the object version instead.) Just to clarify, what Ethan has is not incorrect: the way he's constructing the where clause requires at least one expression before the $allowed_fields gets tacked on: $query .= AND ($key = '$val') ; Since if even one of those gets appended, there must be an expression before the first AND. The 1 merely evaluates to true here, creating a valid where clause. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] School timetable in php
On Fri, Apr 20, 2012 at 7:00 AM, Vinay Kannan viny...@gmail.com wrote: I am trying to develop a school time table system in php mysql, anybody has any headsup on the same, i was thinking about some existing scripts to take a look at. Not entirely clear what is meant by time table -- do you mean schedule of classes? Important dates in the school year? or...? Also, what is it you want to be able to do with this? Who will be using this? There are many different approaches one can take, depending on what needs to happen. Hard to know what to suggest without a little more info? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Invitation from Multiply
2012/4/18 MA PROPOSISION ELMAZI (via Multiply) multi...@multiply.com: Check out my Multiply site oh i can hardly wait to see this... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple result sets with a prepared statement in mysqli
On Fri, Apr 6, 2012 at 12:33 PM, Ken Keefe kjke...@illinois.edu wrote: I am trying to use mysqli's prepared statements to execute a stored procedure that returns multiple result sets. I've been struggling with this for some time now. I have created a short example test script that demonstrates the problem. The script can be found here: http://pastebin.com/YZtDCNMF This code is how I expect mysqli to handle this scenario. However, I get a warning on line 31 that the number of bind variables doesn't match the number of fields in the prepared statement. This means that the second result was not fetched by the call on line 29. Any help on this issue would be greatly appreciated! Also, I'm using the packaged version of PHP that comes on Ubuntu 10.04 (5.3.2). Thanks, Ken Thinking off-the-cuff here, but I'm not sure multiple selects work that way in a stored procedure. Don't stored procedures just return the last set? Maybe someone with more familiarity with them can answer. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple result sets with a prepared statement in mysqli
On Fri, Apr 6, 2012 at 2:02 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Fri, Apr 6, 2012 at 12:33 PM, Ken Keefe kjke...@illinois.edu wrote: I am trying to use mysqli's prepared statements to execute a stored procedure that returns multiple result sets. I've been struggling with this for some time now. I have created a short example test script that demonstrates the problem. The script can be found here: http://pastebin.com/YZtDCNMF This code is how I expect mysqli to handle this scenario. However, I get a warning on line 31 that the number of bind variables doesn't match the number of fields in the prepared statement. This means that the second result was not fetched by the call on line 29. Any help on this issue would be greatly appreciated! Also, I'm using the packaged version of PHP that comes on Ubuntu 10.04 (5.3.2). Thanks, Ken Thinking off-the-cuff here, but I'm not sure multiple selects work that way in a stored procedure. Don't stored procedures just return the last set? Maybe someone with more familiarity with them can answer. A little digging, since I was also interested, reveal the following: At the bottom of [1]: Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1. The client should also specify the CLIENT_MULTI_RESULTS option when it connects. For C programs, this can be done with the mysql_real_connect() C API function. See Section 20.8.3.52, “mysql_real_connect()”, and Section 20.8.12, “C API Support for Multiple Statement Execution”. And digging around [2]: Handling result sets Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query(). The mysqli_query() function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query() to fail returning the user expected result sets. Result sets returned from a stored procedure are fetched using mysqli_real_query() or mysqli_multi_query(). Both functions allow fetching any number of result sets returned by a statement, such as CALL. Failing to fetch all result sets returned by a stored procedure causes an error. Example #3 on page [2] shows the use of retrieving multiple sets from a stored procedure. [1]: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html [2]: http://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
On Thu, Jan 12, 2012 at 1:12 AM, B. Aerts ba_ae...@yahoo.com wrote: Karl, I'm somewhat limited to reading posts, so I might have missed something, but can you explain why you wanted to avoid the 3rd table solution ? Because depending on that question, I can offer two other solutions, but they have their own limitations. Solution 1: the 2,5nd table. you create an extra table and write a procedure that makes a unique ID for every combination of attributes (and this procedure needs to be run every time you update/add attributes). when you want to query items for a combination of attributes, you first select the attribute combination ID, then query your item list for all items with this specific ID. It isn't exactly a 3rd table solution as it doesn't involve individual cross-references between items and a set of attributes, and thus saves you an INNER JOIN. Have you done any performance testing on this to see how your scheme compares to using a 3rd table? Solution 2: an alphanumeric bit-wise selection. Instead of saving your combinations as binary number, you could extend it to, for example, a string of 4 characters per attribute: 4 characters times 26 characters makes 456976 possible combinations per attribute : define MENS = ; define WOMENS = AAAB ; define GREEN = ; define RED = AAAB ; you write one PHP function that converts an array of combinations into an ISO wildcard filter (or does the MySQL syntax provide regular expressions ?), and apply this in a SELECT FROM ... WHERE ... LIKE statement. $findTheseAttribs = new array ( MENS, GREEN ); $SQLlike=createSQLwildcard($findTheseAttribs); // returns $mySql = SELECT * FROM t_myTable WHERE itemAttribute LIKE . $SQLlike; The advantage is that you can assign different filter widths per attribute: MENS/WOMENS only need 1 character, size only needs 2 ( XXXS, XXS, XS, S, ... pretty limited), while colour can have op to 10 characters to encode. This leaves reasonable room to expand/scale. The drawback is that, when you add new attributes, your filter string expands, and the LIKE-statement might fail on the difference in string lengths. Bert On 12/01/12 02:18, Karl DeSaulniers wrote: Yeah, I was being somewhat facetious about the colors of a shirt. :) I agree on the items and attributes drill-down before implementation though. There will be more than T-Shirts. Watches, book-covers, etc, etc. So I need to find a general logic to cover the items and attributes of each item? And this will make it scalable? Best, On Jan 11, 2012, at 4:39 PM, tamouse mailing lists wrote: I am thinking of limiting the colors to 10 for now (after all there are only so many ways to die a shirt. =) Oh, please. There are lots more than 10 dyes in the world. Take a look at a women's clothing catalog sometime or other... Just look at this one t-shirt item alone: http://store.americanapparel.net/2102.html Individual item characteristics are going to be a lot different than categories. You'll need expandable attributes for all kinds of things. Colour is the obvious one here. Also: Size: not everything comes in S, M, L, or is measured in that way. If this is for an apparel store that sells a variety of different items, you'll need to solve this generally across a whole lot of different types of clothing. I'd really suggest you do a deep analysis of the different types of items that are going to be sold, the attributes of each one, and figure out how to best represent that breadth and depth. Karl DeSaulniers Design Drumm http://designdrumm.com -- 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
Re: [PHP-DB] Prepared Statements
On Fri, Nov 11, 2011 at 10:41 AM, Ron Piggott
ron.pigg...@actsministries.org wrote:
?php
$dsh = 'mysql:host=localhost;dbname='.$database;
$dbh = new PDO($dsh, $username, $password);
#query for the authorization code
$query = SELECT `authorization_code` FROM
`directory_listing_update_authorizations` WHERE NOW() BETWEEN `start_date`
AND `end_date` AND `authorization_code` = :authorization_code AND
`directory_entries_reference` = :directory_entries_reference LIMIT 1;;
$stmt = $dbh-prepare($query);
$stmt-bindValue(':directory_entries_reference',
$directory_entries_reference, PDO::PARAM_STR);
$stmt-bindValue(':authorization_code', $authorization_code, PDO::PARAM_STR);
$stmt-execute() or die(print_r($stmt-errorInfo(), true));
while ($row = $stmt-fetch()) {
Not entirely clear here why you need a while statement if your query
above is limit 1.
if ( $row['authorization_code'] == $authorization_code ) {
This is redundant with the query statement above; given that, this
will always pass.
#update directory_entries.last_review with today's date
$query = UPDATE `directory_entries` SET `last_review` = NOW() WHERE
`reference` = :directory_entries_reference LIMIT 1;;
$stmt = $dbh-prepare($query);
This concerns me -- the outer loop is using the previously prepared
and exectued statement in the $stmt variable, then you're resetting it
here. This probably works because your initial query was limit 1, but
it might not in another context. Regardless, it makes the outer while
loop test invalid.
$stmt-bindValue(':directory_entries_reference',
$directory_entries_reference, PDO::PARAM_STR);
$stmt-execute() or die(print_r($stmt-errorInfo(), true));
} else {
#failure, direct user to request new authorization code or login
manually
}
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need help with updating existing mysql records with something else
2011/11/11 Amit Tandon att...@gmail.com:
U can update the record with following
===
$result = mysql_query(UPDATE tax set mwool40_
totqty = $res, $connection) or die(error
updating database);
Won't this update every row in the table?
I think you'd want:
$result = mysql_query(UPDATE tax SET mwool40_totqty = $res
WHERE id=.$res['id'],$connection) or die(blah blah);
Where id is the identifier of the current record you're looking at
(however you've done that).
if (mysql_affected_rows($connection) != 1 (/* no of rows that should be
updated */)
{
die(Update problem) ;
}
The only issue is the where condition that you would put in the query
UPDATE tax set mwool40_totqty = $res where ...
^This. Don't leave this out.
regds
amit
The difference between fiction and reality? Fiction has to make sense.
2011/11/11 Guru™ nagendra802...@gmail.com
Hi All,
I want to update an existing mysql record with another value after
calculating it. Below is the code.
?php
$connection = mysql_connect(localhost, root, ) or die(Error
connecting to database);
mysql_select_db(maha, $connection);
$result = mysql_query(SELECT * FROM tax, $connection) or die(error
querying database);
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){
?
tr ?php if($i%2 == 1){ echo class='body2'; }else{echo
class='body1';}?
td
?php
$res=$result_ar['mwool40_totqty']-10;
echo $res;
?/td
/tr
?php
$i+=1;
}
?
I want to append the $mwool40_totqty value with the calculated one $res.
Let say initially the value of $mwool40_totqty is 50. and after calculation
its value became 40, then the code should edit/alter/update the value in
mysql table with integer 40. Please help me with this.
--
*Best,
*
*Guru™*
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] question about best practice for coding sql statements
I'm curious how others feel about this. When I'm creating an SQL string, either for a non-prepared or prepared execution, I build it in a variable and then pass the variable to the query or prepare function. This lets me easily add something to view the SQL statement, and also if there's an error, emit the SQL statement. Thus: $sql = SELECT * FROM TABLE WHERE id=$id; if ($debug) error_log($sql. from .__FILE__.' at '.__LINE__' in '__FUNCTION__.PHP_EOL); // just an example $rslt = $db-query($sql) or die(Could not get result from $sql: .$db-errno.: .$db-error...PHP_EOL); and so on... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Simple MySQL sample code runs out of memory
On Thu, Oct 27, 2011 at 10:04 PM, p...@umpquanet.com wrote: Something more fundamental is going on. Here is simpler code which still reproduces the problem. I get: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20 bytes) in xx3.php on line 26 What's up with that? Hmm, okay, I haven't worked with such huge datasets in MySQL and PHP before, so now I'm speculating here. If the dataset is so huge that it's eating up all your memory, perhaps you need to cycle through the data base using limits, and process it in chunks, and release each dataset as you're done processing it. I know I've had to do that with Oracle databases before that were storing 100's of millions of records. It's rather like pagination for displaying data, only in this case you can have much much higher chunks than a displayed page. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Simple MySQL sample code runs out of memory
On Thu, Oct 27, 2011 at 6:59 PM, p...@umpquanet.com wrote:
Running PHP 5.3.5 on FreeBSD 8.2 connecting to a MySQL 5.1.55
server.
Why does this code (below) run out of memory? It queries
test_table for all rows, all fields and sorts them by the numeric
'contract' field. It then iterates through the rows, and tallies
the number of rows per contract. That's not the end goal of the
processing, but the processing does not require storing multiple
rows in memory (except in the MySQL result buffer). One row at a
time in memory is all I need.
The schema is not huge:
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| contract | int(11) | YES | | NULL | |
| A | int(8) unsigned | NO | | 0 | |
| B | datetime | YES | | NULL | |
| C | int(8) unsigned | YES | | 0 | |
| D | char(8) | YES | | NULL | |
| E | char(8) | YES | | | |
| F | int(4) | YES | | 0 | |
| G | int(1) | YES | | 0 | |
| H | char(8) | YES | | 00:00 | |
| I | varchar(100) | YES | | XXX | |
+--+-+--+-+-+---+
?php
require_once( '../include/mysql_funcs.php' );
$db_host = $test_db_host;
$db_user = $test_db_user;
$db_name = $test_db_name;
$db_pwd = $test_db_pwd;
if (!($db_conn = mysql_connect( $db_host, $db_user, $db_pwd )))
die( Can't connect to MySQL server\n );
if (!mysql_select_db( $db_name, $db_conn ))
die( Can't connect to database $db_name\n );
$qry = select * from test_table order by contract;
if ($result = mysql_query( $qry, $db_conn )) {
$row = mysql_fetch_array( $result );
while ($row) {
$c = $row[ 'contract' ];
$n = 0;
while ($row ($c == $row[ 'contract' ])) {
$n++;
$row = mysql_fetch_array( $result );
}
echo sprintf( |%13d |%7d |\n, $c, $n );
} // while
} else {
die( mysql_error() . \n );
}
?
The output ends with:
...
| 39582 | 518 |
| 39583 | 384 |
| 39584 | 429 |
| 39585 | 433 |
| 39586 | 359 |
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to
allocate 20 bytes) in xx2.php on line 26
Line 26 seems to be the innermost mysql_fetch_array call.
The sum of the right-hand column for all the output produced
prior to running out of memory is 274547. There are 295287 total
rows in the table.
What can I change to prevent this code from running out of memory
even with an arbitrarily large number of rows returned from the
'select' query?
Thank you for your time!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
I don't use the mysql functions, but rather the mysqli functions,
where you have to free resources (query results) when you're done with
them. Do you have to do the same thing with msql ?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Simple MySQL sample code runs out of memory
On Thu, Oct 27, 2011 at 9:36 PM, p...@umpquanet.com wrote:
On Thu, Oct 27, 2011 at 09:17:21PM -0500, tamouse mailing lists wrote:
That said, I think there must be a way to do this in SQL.
Absolutely, there's a way to do this in SQL;
select contract, sum(1) from test_table group by contract
But as I said, simply counting is not the intent of the actual
processing that will be done.
I need to find a method that can iterate through a large number
of records and process each record (in whatever way I choose to
define) without running out of RAM. I only need to access one
record at a time, so I thought this would be pretty trivial,
and was surprised to find that the routine ran out of memory.
This simple counting exercise is merely a test of the loop
algorithm so that I can do a 'cmp' or 'diff' comparison of the
output of the PHP versus the output of the SQL to verify that in
fact the PHP algorithm is indeed finding and processing each
record (and thus arriving at the same per-contract counts that
the SQL statement does).
I will experiment with your suggestions.
Thank you,
Jim
Ah, okay, did not get that -- sorry.
Still, an inner and outer loop don't really seem necessary here, as
you're spinning through one set of data, there doesn't seem a need to
run through it that way. A more traditional method is to do something
like:
$last_contract = '';
while ($row = $msql_fetch_assoc($results) {
if ($last_contract == $row['contract]) {
// do what ever you'd do with the same contract
} else {
$last_contract = $row['contract'];
// do what ever you'd do with the new contract
}
}
assuming you are ordering by contract as in the example select.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
On Fri, Oct 21, 2011 at 2:09 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: tamouse mailing lists [mailto:tamouse.li...@gmail.com] Sent: 20 October 2011 21:37 On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three- part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month Look again. Month wrap *is* handled by the specific tests for start_month and end_month. Hmm -- yes, you are right -- it does handle the month wrap problem okay. As to year-wrap, Ron's original post said: ... The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. so I didn't bother about year-wrap, assuming he would include a range with start_date of 1/1 and another with end_date of 31/12. So you are saying it can be easily worked around if there is a particular case that is supposed to wrap over the end of the year and simply include the item twice: one starting on Jan 1 and the other one ending on Dec 31. (I'm not sure if that's what you meant above.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: SELECT
On Mon, Oct 17, 2011 at 12:58 PM, Jim Giner jim.gi...@albanyhandball.com wrote: I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) Hmm, no this won't work. Check this: start_month=10 start_day=15 end_month=1 end_day=1 sel_day=23 sel_month=12 in this case, sel_month IS greater than start_month and sel_day IS greater than start_day, HOWEVER sel_month is ALSO greater than end_month and sel_day is ALSO greater than end_day in this case. (wrap around to new year problem, wrap around to new month problem) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three-part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month This might be best handled in a stored procedure, converting the values stored in the table to dates to do the comparison with in the where clause. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
On Thu, Oct 20, 2011 at 3:36 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three-part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month This might be best handled in a stored procedure, converting the values stored in the table to dates to do the comparison with in the where clause. In thinking further on this, the OP might consider this problem as well -- it is going to be difficult to determine the correct response if all that is stored is the start and ending month and day of month in the case where the desired time stretch wraps over to the new year. When your start month is 12 and your end month is 1, what do you expect to happen? It can't generally be solved by using current year for the start and current year + 1 for the end. For example, you may want to have something start at current year, month=6 and end as next year, month=8, so simply checking if end month start month won't give you the ability to discern if you've wrapped the year. (I realize this may not be the OP's case, but it is still an issue if seeking a general solution.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
