[PHP-DB] Getting a date from MS SQL in the correct format.
I want to extract a date from a datetime field in Microsoft SQL, via ODBC ('cause we support different databases, or so I'm told, and hey, what do I know! ). I want the format to be CCYYMMDD. Is there a MS SQL function for this? I've found DATEPART("",field), but I don't know how to get all the bits (i.e. , MM, DD). I get a numerical addition. If I CAST as varchar, I don't get a 0 for <10 on MM and DD. i.e. 1st January 2001 comes out as 200111 rather than 20010101 I know there must be a format function, but SQL novice here can't find it. Any suggestions. In the mean time, I've had to use DATEPART to pull the three bits in separately and add a bit to the PHP templates to add them all together. Ideally I want just the value as CCYYMMDD. Regards, Richard Quadling. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Which ORM tool for Windows with Microsoft SQL Server 7/2000/2005.
Hi. I'm trying to find an ORM tool to allow me to talk to Microsoft SQL servers (V7, 2000 and 2005) for Windows. For those that use ORM, what do you use? I've looked at Doctrine (requires php_pdo_mssql which isn't part of the VC9 win32 builds; for the VC6 builds, it requires ntwdblib.dll which is oh so dead). I'm looking into Propel next. This now seems to be using PDO and PDO_MSSQL, so it seems I've got the same problems. Ideally, using the nice shiny Microsoft SQL Server 2005 Driver for PHP from Microsoft (http://www.codeplex.com/SQL2K5PHP) would be nice. But this isn't PDO. Is there anyone capable of getting the Microsoft driver PDO aware? Any help/suggestions would be appreciated. Regards, Richard Quadling. -- - Richard Quadling Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 "Standing on the shoulders of some very clever giants!" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: php-win-which-orm-tool-for-windows-with.html
2009/4/14 Alvaro Carrasco : > Hi Richard, > > Sorry about not sending to the list, I found this post while browsing the > internet and i'm not subscribed to the php-windows list. > >> Hi. >> >> I'm trying to find an ORM tool to allow me to talk to Microsoft SQL > >> servers (V7, 2000 and 2005) for Windows. >> >> For those that use ORM, what do you use? >> > > I'm the main developer of Outlet ORM and I currently use it with SQL Server > 2005. Outlet relies on PDO and I use the PDO_ODBC driver when connecting to > SQL Server from windows. Here is the link to the project: > > http://www.outlet-orm.org > >> I've looked at Doctrine (requires php_pdo_mssql which isn't part of >> the VC9 win32 builds; for the VC6 builds, it requires ntwdblib.dll > >> which is oh so dead). >> >> I'm looking into Propel next. This now seems to be using PDO and >> PDO_MSSQL, so it seems I've got the same problems. >> >> Ideally, using the nice shiny Microsoft SQL Server 2005 Driver for PHP > >> from Microsoft (http://www.codeplex.com/SQL2K5PHP) would be nice. But >> this isn't PDO. >> >> Is there anyone capable of getting the Microsoft driver PDO aware? > > > I'm planning on wrapping the new 2005 driver with a PDO-interface. In case > you're interested, I'll post my progress to the outlet mailing list at: > http://groups.google.com/group/outlet-orm > > >> >> Any help/suggestions would be appreciated. >> >> Regards, >> >> Richard Quadling. > > Feel free to redistribute this email if you want. > > > Alvaro Carrasco > > Thank you to Alvaro for this. Alvaro, will you be able to submit your wrapper to PHP so that it can become part of the core release? Currently, for VC9, there is no php_mssql or php_pdo_mssql (php-5.3-win32-VC9-x86-latest as at 2009/04/15 04:50) A replacement is needed. Regards, Richard Quadling. -- - Richard Quadling Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 "Standing on the shoulders of some very clever giants!" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP-WIN] Which ORM tool for Windows with Microsoft SQL Server 7/2000/2005.
2009/4/20 Andi Gutmans : > Btw, in Zend Framework while we based our adapter interface on PDO we allow > for adapters which are not based on PDO (via quackable interfaces). > DB2, Oracle and MySQLi are supported in that way as well as of course many > PDO drivers. > You can easily take one of those native DB adapters and make them work with > the sql server for PHP driver. > Andi > >> -Original Message- >> From: David Sceppa [mailto:david.sce...@microsoft.com] >> Sent: Sunday, April 19, 2009 5:18 PM >> To: Richard Quadling >> Cc: php-db@lists.php.net; php-wind...@lists.php.net; php-windows- >> intern...@lists.php.net >> Subject: RE: [PHP-WIN] Which ORM tool for Windows with Microsoft SQL >> Server 7/2000/2005. >> >> Richard, >> >> As pointed out on other branches of the thread, PDO_ODBC is currently >> your best option for accessing SQL Server via PDO and technologies that >> rely on PDO. We are investigating producing a PDO driver for SQL >> Server Driver going forward. >> >> David Sceppa >> Program Manager - Microsoft SQL Server Driver for PHP >> >> -Original Message- >> From: Richard Quadling [mailto:rquadl...@googlemail.com] >> Sent: Tuesday, April 14, 2009 7:38 AM >> To: php-db@lists.php.net; php-wind...@lists.php.net; php-windows- >> intern...@lists.php.net >> Subject: [PHP-WIN] Which ORM tool for Windows with Microsoft SQL Server >> 7/2000/2005. >> >> Hi. >> >> I'm trying to find an ORM tool to allow me to talk to Microsoft SQL >> servers (V7, 2000 and 2005) for Windows. >> >> For those that use ORM, what do you use? >> >> I've looked at Doctrine (requires php_pdo_mssql which isn't part of >> the VC9 win32 builds; for the VC6 builds, it requires ntwdblib.dll >> which is oh so dead). >> >> I'm looking into Propel next. This now seems to be using PDO and >> PDO_MSSQL, so it seems I've got the same problems. >> >> Ideally, using the nice shiny Microsoft SQL Server 2005 Driver for PHP >> from Microsoft (http://www.codeplex.com/SQL2K5PHP) would be nice. But >> this isn't PDO. >> >> Is there anyone capable of getting the Microsoft driver PDO aware? >> >> Any help/suggestions would be appreciated. >> >> Regards, >> >> Richard Quadling. >> >> -- >> - >> Richard Quadling >> Zend Certified Engineer : >> http://zend.com/zce.php?c=ZEND002498&r=213474731 >> "Standing on the shoulders of some very clever giants!" >> >> -- >> PHP Windows Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > > Thank you all for your responses. I'm particularly encouraged to see the response from David @ Microsoft. My time for learning about using an ORM is limited, so I'm going to try out Outlet as this supports an "out of the box" experience (ahem). Regards, Richard. -- - Richard Quadling Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 "Standing on the shoulders of some very clever giants!" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Cant get Mysql stored procedure to work.
2009/7/13 Patrick Moloney : > Bastien Koert wrote: >> >> On Mon, Jul 13, 2009 at 11:52 AM, Patrick Moloney >> wrote: >>> >>> I've had no success trying to execute any stored procedure from php. >>> I'm just trying to follow the example for multi-query in the php manual, >>> and >>> have tried an example from Artfulsoftware.com. I have one input and one >>> output parameter. I've simplified the stored procedure to always output >>> one >>> value from one record, regardless of the value passed in. >>> The SP works in Mysql. Just cant run it from php. It returns False. >>> I look at the query log in mysql and it calls the stored procedure, and >>> shows the input value and output variable. No further information. I'm >>> not >>> familiar with that log so I don't know what it should look like. >>> The only thing I see looks like a brief reference to UTC8 - but I don't >>> think I am (intentionally) using that anywhere. I get no other error back >>> or >>> logged. I'm using root as the user, which is the only defined user. >>> Should >>> this work, or are stored procedures not working well? >>> >>> -- >>> PHP Database Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >>> >>> >> >> Can you show the php code? >> > > Below is the relevant code. The database connection is established, but the > query call returns False. The call is recorded in the mysql query log. No > error there or returned, but just False. Proc works in mysql and ignores > the value of the input parameter and returns 1 string. But I can't get it to > do that from php. > > > �...@$db = new mysqli('localhost', 'root', 'password', 'Sales'); > if (mysqli_connect_errno()) > { echo 'Error: could not connect to database. '; > exit; > } > > $brandrequest = 'x'; > $res = $db->multi_query( "CALL myproc($brandrequest, ModelID); " ); > if (!$res ) { > echo 'No Listings were found for this request.'; > echo mysql_error(); > exit; > } > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Isn't your SQL is broken? CALL myproc(x, ModelID) ? Surely ... CALL myproc('x', 'ModelID') -- - Richard Quadling Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 "Standing on the shoulders of some very clever giants!" ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Cant get Mysql stored procedure to work.
2009/7/13 Patrick Moloney : > Bastien Koert wrote: >> >> On Mon, Jul 13, 2009 at 1:33 PM, Patrick Moloney >> wrote: >>> >>> Bastien Koert wrote: >>>> >>>> On Mon, Jul 13, 2009 at 11:52 AM, Patrick Moloney >>>> wrote: >>>>> >>>>> I've had no success trying to execute any stored procedure from php. >>>>> I'm just trying to follow the example for multi-query in the php >>>>> manual, >>>>> and >>>>> have tried an example from Artfulsoftware.com. I have one input and one >>>>> output parameter. I've simplified the stored procedure to always output >>>>> one >>>>> value from one record, regardless of the value passed in. >>>>> The SP works in Mysql. Just cant run it from php. It returns False. >>>>> I look at the query log in mysql and it calls the stored procedure, and >>>>> shows the input value and output variable. No further information. I'm >>>>> not >>>>> familiar with that log so I don't know what it should look like. >>>>> The only thing I see looks like a brief reference to UTC8 - but I don't >>>>> think I am (intentionally) using that anywhere. I get no other error >>>>> back >>>>> or >>>>> logged. I'm using root as the user, which is the only defined user. >>>>> Should >>>>> this work, or are stored procedures not working well? >>>>> >>>>> -- >>>>> PHP Database Mailing List (http://www.php.net/) >>>>> To unsubscribe, visit: http://www.php.net/unsub.php >>>>> >>>>> >>>> Can you show the php code? >>>> >>> Below is the relevant code. The database connection is established, but >>> the >>> query call returns False. The call is recorded in the mysql query log. No >>> error there or returned, but just False. Proc works in mysql and ignores >>> the value of the input parameter and returns 1 string. But I can't get it >>> to >>> do that from php. >>> >>> >>> @$db = new mysqli('localhost', 'root', 'password', 'Sales'); >>> if (mysqli_connect_errno()) >>> { echo 'Error: could not connect to database. '; >>> exit; >>> } >>> >>> $brandrequest = 'x'; >>> $res = $db->multi_query( "CALL myproc($brandrequest, ModelID); " ); >>> if (!$res ) { >>> echo 'No Listings were found for this request.'; >>> echo mysql_error(); >>> exit; >>> } >>> >>> >>> -- >>> PHP Database Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >>> >>> >> >> Is the multi_query call the correct one? >> > > I did work with mysqli_multi_query also. The documentation seemed to confuse > the two. The only examples were like I have above. > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If your stored procedure has in and out parameters, maybe the mysqli::prepare() method may be of more use. -- - Richard Quadling Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 "Standing on the shoulders of some very clever giants!" ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Image_Problem
2009/7/22 Bilal Ahmad : > Hi All, I wanna ask a question pleasenV. I am trying to create an image on > fly, please do help me , following is the code. > > *File Name : Font.php > Code: * > > > > Image Creation > > > var xmlhttp; > > function showPic() > { > xmlhttp=GetXmlHttpObject(); > if (xmlhttp==null) > { > alert ("Browser does not support HTTP Request"); > return; > } > > var text = document.getElementById("textfield").value; > > var url="image.php"; > url=url+"?text="text; > alert(url); > url=url+"&sid="+Math.random(); > xmlhttp.onreadystatechange=stateChanged; > xmlhttp.open("GET",url,true); > xmlhttp.send(null); > } > > function stateChanged() > { > if (xmlhttp.readyState==4) > { > if(xmlhttp.responseText == 1) > { > document.getElementById("pic").style.display="block"; > } > } > } > > function GetXmlHttpObject() > { > if (window.XMLHttpRequest) > { > // code for IE7+, Firefox, Chrome, Opera, Safari > return new XMLHttpRequest(); > } > if (window.ActiveXObject) > { > // code for IE6, IE5 > return new ActiveXObject("Microsoft.XMLHTTP"); > } > return null; > } > > > > > > > Text > > > Text > > > > > > > > onClick="showPic()"> > > > > > /> > > > > > > > *File Name : image.php > Code:* > > [php] > > $name = $_GET['text']; > > $pic = imagecreatetruecolor(100, 100); > $text_color = imagecolorallocate($pic, 255, 255, 255); > imagestring($pic, 10, 15, 15, $name, $text_color); > $pi = Imagejpeg($pic,"pic.jpg"); > echo $pi; > ImageDestroy($pic); > > [/php] > > *Problem: * > > What this code is doing is that, it creates a new image with the text (that > user enters) on it, but loads the image that was created previously, I want > that it should display the text on the picture which users enter on the fly. > ( e.g. If user enter TEXT as text it should display TEXT on the picture > displayed, soon after we get response from ajax).I hope you got the problem. > > > Thanks > Try changing ... $pi = Imagejpeg($pic,"pic.jpg"); echo $pi; to ... imagejpeg($pic); -- - Richard Quadling Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 "Standing on the shoulders of some very clever giants!" ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Need help-Send email
2009/9/14 Chris : > nagendra prasad wrote: >> >> Hi all, >> >> Below is the code that I have coded after replies from all of you. But >> before that I want to thank all of you for your help. I have coded the >> email >> code but still it is giving me an error. Please help me. > > ... and the error is what exactly? > >> > >> //sending email script >> >> $to = "exam...@gmail.com"; >> $subject = "Email from admin"; >> >> if ($_POST['submit']) >> { >> //get data from form >> >> $name = $_POST['name']; >> $message = $_POST['message']; >> if ($name&&$message) >> { >> $namelen = 20; >> $messagelen = 300; >> if (strlen($name)<=$namelen && strlen($message)<=$messagelen) >> { >> //everything is ok >> >> ini_set("SMTP", "smtp.gmail.com"); > > Where's your smtp authentication? You can't just set the mail server and > that's it. > > http://deepakssn.blogspot.com/2006/06/gmail-php-send-email-using-php-with.html > > Use phpmailer (as this article suggests) and you'll have a lot better chance > of things working. > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Follow these steps... 1 - What is the domain of the recipient? gmail.com 2 - What is recipients SMTP server? nslookup -q=mx gmail.com [2009/09/14 10:10:58] [C:\] [] >nslookup -q=mx gmail.com Server: bv-vm-svr-1.bandvulc.local Address: 10.0.30.1 Non-authoritative answer: gmail.com MX preference = 40, mail exchanger = alt4.gmail-smtp-in.l.google.com gmail.com MX preference = 10, mail exchanger = alt1.gmail-smtp-in.l.google.com gmail.com MX preference = 5, mail exchanger = gmail-smtp-in.l.google.com gmail.com MX preference = 30, mail exchanger = alt3.gmail-smtp-in.l.google.com gmail.com MX preference = 20, mail exchanger = alt2.gmail-smtp-in.l.google.com alt4.gmail-smtp-in.l.google.com internet address = 72.14.247.27 alt1.gmail-smtp-in.l.google.com internet address = 209.85.129.114 gmail-smtp-in.l.google.com internet address = 74.125.79.114 alt3.gmail-smtp-in.l.google.com internet address = 209.85.223.14 alt2.gmail-smtp-in.l.google.com internet address = 209.85.218.33 Use one of the servers you see here. These are the public SMTP servers set to RECEIVE email from anyone. No authority required. What _IS_ required is a valid recipient. The output of the nslookup command can also be seen by using getmxrr() in PHP. Look at the user notes on http://docs.php.net/getmxrr for versions of getmxrr() for versions of PHP on windows which didn't have this function. At a fundamental level, this is how email is sent. You normally send it to a server you are allowed to send to (a relay). This normally requires some sort of security (not always - but may require you to POP3 login to your mailbox). You send the mail. The local server receives it. By using the domain of the recipient the server finds where to send it. The recipients server knows nothing about YOUR server - no authority - but has to accept the message. Using getmxrr() is bypassing the local relay step and going direct. Obviously, you have to handle all the errors, retries, etc. But you can also step through the servers if one is offline or whaterver. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] scheduler
2009/9/23 Vinay Kannan : > Hi, > > I am developing a web application in PHP, the db is mysql. > > I want to develop something like a batch file, which would run everyday, > query mysql and throw the output to another table which would then perform > some additional functions. > > Is something like this possible?? i am working on windows thats why said a > batch,but not rigid about the batch, it cud even be Java. > > > Thanks, > Vinay > Take a look at http://docs.php.net/manual/en/install.windows.commandline.php for using PHP from the command line on windows. The command lines you use are the same for the task scheduler. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] scheduler
2009/9/23 Vinay Kannan : > Thank You Neil ! > > So if I write in a command like C:\php\php.exe C:\path\to\script.php in a > batch file, and have another batch file to run it at given time frames, then > the PHP file would make the updates and perform the functionalities that its > required to do, is it? coz what I have on my mind is that the tasks are run > and the functions are performed even if theres no one to run in the command, > that can be done right? > > Vinay Kannan. > > On Wed, Sep 23, 2009 at 5:40 PM, Niel Archer wrote: > >> > Hi, >> > >> > I am developing a web application in PHP, the db is mysql. >> > >> > I want to develop something like a batch file, which would run everyday, >> > query mysql and throw the output to another table which would then >> perform >> > some additional functions. >> > >> > Is something like this possible?? i am working on windows thats why said >> a >> > batch,but not rigid about the batch, it cud even be Java. >> >> Not only possible but very simple. Just write the php as normal and use >> Windows' Scheduler to run it with something like: >> >> C:\php\php.exe C:\path\to\script.php >> >> I have several scripts like this scheduled to do basic maintenance work >> or carry out automated tasks. >> >> > Thanks, >> > Vinay >> >> -- >> Niel Archer >> niel.archer (at) blueyonder.co.uk >> >> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > Save yourself the batch files. Just use ... C:\php\php.exe -f C:\path\to\script.php -- as the command. The -f is for completeness. If you want to pass parameters to your script, put them after the -- e.g. C:\php\php.exe -f C:\path\to\script.php -- --scriptparam1 --scriptparam2 The task scheduler is a windows service. It runs even if you are not logged on. Richard. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How exactly do I get UTF-8 data into SQL Server on Windows via ODBC SQL Native Client 10.0?
Hi. I'm trying to get data into an MS SQL Server 2005 database. The data is coming to me as UTF-8 encoded data. The data is primarily European languages (so I've got a few accented characters), but I know I've got Korean, Vietnamese and some Japanese coming later. I'm using PHP 5.3.2-dev. The SQL Server is 2005 and the ODBC driver I'm using is SQL Native Client 10.0 I've cut'n'pasted my table and SP below... I'm calling the SP (all the phone numbers have been edited) ... EXEC PhoneBilling.dbo.usp_HandleDirectory @s_PhoneNumber = 'n', @s_Recipient = N'Vergölst GmbH (Business)', @b_Rechargeable = 1, @b_AllowOverride = 0 I've tried using ... $s_Recipient = mb_convert_encoding($s_Recipient, 'UCS-2', 'UTF-8'); But this produces a string with nulls in which terminates the sql string inappropriately. I've also tried UTF-16 If I do nothing, the data in SQL is ... 45639 n Vergölst GmbH (Business) 1 0 2009-10-07 08:29:42.137 45641 m Vergölst GmbH (Mobile) 1 0 2009-10-07 08:29:42.150 If I execute the above EXEC call in MSSQL Server Management Studio, I get valid results when I select the inserted rows. 48388 o Vergölst GmbH (Business)1 0 2009-10-07 08:44:42.673 48389 p Vergölst GmbH (Business)1 0 2009-10-07 08:46:22.730 which is what I want. I must be missing a trick as I'm sure it can't be that difficult. I'm not in a position to try PHP6 yet. Any suggestions would be gratefully received. Regards, Richard Quadling. CREATE TABLE [dbo].[Phones_Directory]( [UniqueID] [int] IDENTITY(1,1) NOT NULL, [PhoneNumber] [varchar](20) NOT NULL, [Recipient] [nvarchar](255) NULL, [Rechargeable] [bit] NOT NULL CONSTRAINT [DF_Phones_Directory_Rechargeable] DEFAULT ((1)), [AllowOverride] [bit] NOT NULL CONSTRAINT [DF_Phones_Directory_AllowOverride] DEFAULT ((0)), [Added] [datetime] NOT NULL, CONSTRAINT [PK_Phones_Directory] PRIMARY KEY CLUSTERED ( [UniqueID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] and ALTER PROCEDURE [dbo].[usp_HandleDirectory] @s_PhoneNumber varchar(20) = Null, @s_Recipient nvarchar(255) = Null, @b_Rechargeable bit = 1, -- Rechargeable by default. @b_AllowOverride bit = 0 -- Not overridable by default. AS BEGIN DECLARE @tbl_Results TABLE ( UniqueID int, Result int ) SET NOCOUNT ON; IF NOT EXISTS ( SELECT UniqueID FROM Phones_Directory WHERE @s_PhoneNumber = PhoneNumber ) BEGIN INSERT INTO Phones_Directory ( PhoneNumber, Recipient, Rechargeable, AllowOverride, Added ) VALUES ( @s_PhoneNumber, @s_Recipient, @b_Rechargeable, @b_AllowOverride, GetDate() ) INSERT INTO @tbl_Results VALUES ( SCOPE_IDENTITY(), 1 -- Indicates a new entry ) END ELSE BEGIN INSERT INTO @tbl_Results SELECT UniqueID, 2 -- Indicates a pre-existing entry FROM Phones_Directory WHERE @s_PhoneNumber = PhoneNumber END -- Return the data SET NOCOUNT OFF SELECT CAST(Results.Result AS int) AS 'Result', Phones_Directory.* FROM @tbl_Results Results LEFT OUTER JOIN Phones_Directory ON Results.UniqueID = Phones_Directory.UniqueID END -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend
Re: [PHP-DB] Does PHP5 support querying an MS Access MDE file?
2009/11/2 Timothy Legg : > Hello, > > I have PHP Version 5.2.6-1+lenny3 > > I have been having difficulty using odbc_connect with an MS Access MDE > database. I do have php5-mysql and php5-odbc installed on this server. > > Due to the rather poor search results regarding the topic of querying MDE > files with PHP, I am becoming increasingly concerned that this file type > is not supported. > > Is it possible to query an MDE file via PHP5 or should I try different > approach to querying the database from our linux server? I am interested > in your thoughts. > > Thanks for your help. > > Tim Legg > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If you have the MS Access ODBC driver installed, then have you tried accessing the MDE as an MDB? -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Does PHP5 support querying an MS Access MDE file?
2009/11/3 Timothy Legg : >> 2009/11/2 Timothy Legg : >>> Hello, >>> >>> I have PHP Version 5.2.6-1+lenny3 >>> >>> I have been having difficulty using odbc_connect with an MS Access MDE >>> database. Â I do have php5-mysql and php5-odbc installed on this server. >>> >>> Due to the rather poor search results regarding the topic of querying >>> MDE >>> files with PHP, I am becoming increasingly concerned that this file type >>> is not supported. >>> >>> Is it possible to query an MDE file via PHP5 or should I try different >>> approach to querying the database from our linux server? Â I am >>> interested >>> in your thoughts. >>> >>> Thanks for your help. >>> >>> Tim Legg >>> >> >> If you have the MS Access ODBC driver installed, then have you tried >> accessing the MDE as an MDB? >> > > Yes, I have. The username and password are arbitrarily set since the > management hasn't provided that data for me yet. But I am not far enough > along to have run into a user rights issue. I get a warning and also an > error upon interpreting the page. I have checked the path to the database > file, and it is correct. I also checked file ownership and readability > permissions, too. So maybe the name of the driver is wrong; it does look > a little verbose to me, but it did come straight from the php.net online > documentation. > > Error messages and source is below. Sorry, I modified the domain name in > the path for privacy. > > Warning: odbc_connect() [function.odbc-connect]: SQL error: > [unixODBC][Driver Manager]Data source name not found, and no default > driver specified, SQL state IM002 in SQLConnect in > /var/www/www.example.com/ht-secure/database_entry/test.php on line 9 > > > $mdbFilename = "/root/tims_db.mde"; > $user = "root"; > $password = "1248163264"; > > $connection = odbc_connect("Driver={Microsoft Access Driver > (*.mdb)};Dbq=$mdbFilename", $user, $password); > > ?> > > Thanks for looking this over, > > Tim Legg > >> >> >> -- >> - >> Richard Quadling >> "Standing on the shoulders of some very clever giants!" >> EE : http://www.experts-exchange.com/M_248814.html >> Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 >> ZOPA : http://uk.zopa.com/member/RQuadling >> > > > You've got odbc, but probably not a driver for MS Access. You need both. I can't seem to find a unix driver for MSAccess. Converting to a different DB would help (mysql, sqlite). -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple instances of mysql_connect() in single PHP document.
2009/11/11 Bastien Koert : > On Tue, Nov 10, 2009 at 4:14 PM, Timothy Legg > wrote: >> >> Hello, >> >> Is it wrong to have mysql_connect() called twice within a single PHP >> document, such as this? I have been running into some fairly annoying >> query failures that commute when I change the order of these two blocks of >> code. I am currently working around this by repeatedly using >> mysql_select_db() to change the databases as I am needing to change to a >> different database. >> >> To be more specific, I can use the $db_material handle in doing a query, >> but it will always try to search in the Labor_Log table and thus fail >> because the tables have different and names. Swapping these blocks will >> have a vice-versa effect. >> >> >> //Connect to server (materials) >> $db_material = mysql_connect("localhost", "root", "secret"); >> if (!$db_material) >> { >> echo( "Unable to connect to the database server at this >> time." ); >> exit(); >> } >> mysql_select_db("Material_Log", $db_material); >> >> >> //Connect to server (labor) >> $db_labor = mysql_connect("localhost", "root", "secret"); >> if (!$db_labor) >> { >> echo( "Unable to connect to the database server at this >> time." ); >> exit(); >> } >> mysql_select_db("Labor_Log", $db_labor); >> >> >> >> Tim Legg >> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > If the databases are in the same mysql server, then you could qualify > the table select with the database name and simply re-use the > connection > > select db_name.table_name.field from db_name.table_name [where] > > > -- > > 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 I understand things, if you make 2 or more connections to the same database server with the same credentials, you end up with 1 shared connection. This is assuming you are not using mysql_pconnect() and you are not using the new_link option on mysql_connect(). See http://svn.php.net/viewvc/php/php-src/trunk/ext/mysql/php_mysql.c?view=markup#l864. If you then use change DB on one of the connection, you are changing it for both. The answer is to use a single connection and a fully qualified name to the table. SELECT Alias.Column FROM Database.Table.Column AS Alias sort of thing. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple instances of mysql_connect() in single PHP document.
2009/11/11 Chris : > Andy Shellam (Mailing Lists) wrote: >> >> Hi, >> >>> >>> If the databases are in the same mysql server, then you could qualify >>> the table select with the database name and simply re-use the >>> connection >>> >>> select db_name.table_name.field from db_name.table_name [where] >> >> No offence, but if I saw this in an application's source code, I'd run a >> mile. > > Plus the assumption that they are on the same server and that the user > you're connecting with has access to both databases.. > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If it comes to that, if you are writing SQL code in PHP rather than abstracting it or using stored procedures/views/etc. ... Essentially the issue is 2 identical connections are not actually 2 identical connections. They are 2 references to a single connection. So, changing the DB via 1 resource changes the db for both resources. NOTE: IDENTICAL. If the server or username is different, then that results in a separate connection. http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple instances of mysql_connect() in single PHP document.
2009/11/11 Chris : > Plus the assumption that they are on the same server and that the user > you're connecting with has access to both databases.. See the initial post. $db_material = mysql_connect("localhost", "root", "secret"); $db_labor = mysql_connect("localhost", "root", "secret"); 2 connections using the same server and credentials - results in 1 real connection. $db_material = mysql_connect("localhost", "root", "secret", true); $db_labor = mysql_connect("localhost", "root", "secret", true); should fix it. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] unlink problems
2009/11/25 Neil Jackson : > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > I use the following to delete a file on a webserver > > if( unlink( "./$filename" ) ) > { > echo $filename." deleted "; > $action=''; > echo > "document.location.href='$PHP_SELF?action=$action&code=$code'"; > } > > But I get the following error > Warning: unlink(./6 November 2009.pdf): No such file or directory in > /srv/www/htdocs/bee_partners/admin/tenders.php on line 82 6 November > 2009.pdf not deleted > > Any help? > -BEGIN PGP SIGNATURE- > Version: GnuPG v2.0.9 (GNU/Linux) > Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org > > iEYEARECAAYFAksNBV4ACgkQGgk1S8edB3JqWgCfb61pxGvh+KCXNfNdWgF2kb/P > xCMAn2dSOgN9AiFYX35Ga00XyxZQwGnw > =NL6k > -END PGP SIGNATURE- > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > Not exactly sure what help you would need? The file doesn't exist, so you can't delete it. But a quick hack later ... http://www.beepartners.com/tenders/ is where the files are and http://www.beepartners.com/admin/tenders.php is where the script it. So, "./$filename" should probably be "../tenders/$filename" maybe. Regards, Richard. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] printf and unsecable char
2010/1/4 Chris : > Jean-Philippe Battu wrote: >> >> Hello >> >> I would like to print a string formatted on the right side and fill in >> the left side with the unsecable character [ ] >> >> I read in the printf command I could use : printf( "%#30s" , $mystring ) ; >> to display $mystring string with the character # before (on the left) >> >> and I try to replace # by but I can't find a solution >> >> Do you know the solution to write the command: printf( >> "% 30s" , $mystring ) ; >> or something like that ! > > echo str_repeat(' ', 30); > > (if I understand what you're trying to do correctly). > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > outputs ... Hello, world! -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Im seeking a solution to check for duplicate entries with an other sequence
2010/1/11 omar zorgui : > $sentences[] = "this is a example sentence"; > $sentences[] = "a this is example sentence"; > $sentences[] = "example this is a sentence"; > $sentences[] = "this is a example sentence for a function"; outputs ... Array ( [0] => this is a example sentence [3] => this is a example sentence for a function ) -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Im seeking a solution to check for duplicate entries with an other sequence
2010/1/11 Richard Quadling : > 2010/1/11 omar zorgui : >> $sentences[] = "this is a example sentence"; >> $sentences[] = "a this is example sentence"; >> $sentences[] = "example this is a sentence"; >> $sentences[] = "this is a example sentence for a function"; > > > // Define data. > $Sentences = array > ( > "this is a example sentence", > "a this is example sentence", > "example this is a sentence", > "this is a example sentence for a function", > ); > > // Record hashes of the sorted words in each sentence. > $Hashes = array(); > $Reduced = array_filter > ( > $Sentences, > function($Sentence) > use(&$Hashes) > { > // Explode the sentence into words but forced to lower case. > $Words = explode(' ', strtolower($Sentence)); > > // Sort the words > sort($Words); > > // If the hash of the serialized words array is not already > known > if (!in_array($Hash = md5(serialize($Words)), $Hashes)) > { > // then add it and return true. > $Hashes[] = $Hash; > return True; > } > else > { > // else return false to filter out this sentence. > return False; > } > } > ); > > // Show the reduced sentences. > print_r($Reduced); > ?> > > > outputs ... > > Array > ( > [0] => this is a example sentence > [3] => this is a example sentence for a function > ) > > > > -- > - > Richard Quadling > "Standing on the shoulders of some very clever giants!" > EE : http://www.experts-exchange.com/M_248814.html > Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 > ZOPA : http://uk.zopa.com/member/RQuadling > Is that what you wanted? -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling
Re: [PHP-DB] Automatic logoff
2010/1/27 listread : > Chris, > > Yes, I can see how your suggestion would be good for approximating the > length of a visit, but my issue is more specific and technical... > > We will have users updating database records. We want to lock a record > while it is being worked on and release it once the user is finished or > otherwise leaves the site. > > That's why we want a graceful exit. > > Maybe I should start a new thread about locking db records? > > - Ron > > On 1/26/2010 4:03 PM, Chris wrote: >> >> listread wrote: >>> >>> Bruno, >>> >>> Thanks for the heads up on the php configuration. I'll check that out. >>> >>> We also need to write some data to a database, things like logout time. >>> That means running a script for some other php code. >> >> There are probably a number of situations where the onUnload thing won't >> work including browser crashes, some browsers may not support it, will it >> work if you have multiple browser tabs open and close one (something you'll >> have to research) etc, so be aware that you're not going to get this 100% >> right. >> >> If you just want the timing, I'd do it the other way. >> >> Each time their session is checked (on page load), update the "end" time. >> In db terms: >> >> update session set logout_time=NOW() where session_id='X'; >> >> That way you're always going to get at least an idea of how long their >> session lasts but you won't get "reading time" on the page (ie it takes me 2 >> mins to read something on the page, the logout_time will be 2 minutes before >> I actually close it). >> > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > The technique I've used in the past is semaphore locking, where the semaphore contains the session and the expected expiry time. Follow this. User a starts the process of editing a record. Set the semaphore where there is : a - no existing semaphore - no ongoing edits. b - the semaphore's session is the same - repeat edits by this user in the same session (expired or otherwise). c - the semaphore has expired - the other user simply took too long. If the semaphore cannot be set it will be because of : d - Different non expired session - someone else is editing the record. When a user saves the row, you just remove the semaphore. The semaphores could be in a separate table (rather than on the record itself). Different tables have different number of columns so take different amounts of time to edit, so each table would have a different amount of time from edit to expiry. An entry on a lookup table (just a description) should, in the main, be completed within 30 seconds. But a detail line for a purchase order may take several minutes. You'll have to tune this to your own needs. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Automatic logoff
On 27 January 2010 17:20, listread wrote: > Richard, > > I think I need to learn about semaphores! Any suggestions for a good > tutorial? > > One of the things we want to do is exclude locked records from a query. > Will semaphores provide for that? > > Thanks! > > - Ron > > > > > On 1/27/2010 8:14 AM, Richard Quadling wrote: >> >> The technique I've used in the past is semaphore locking, where the >> semaphore contains the session and the expected expiry time. >> >> Follow this. >> >> User a starts the process of editing a record. >> Set the semaphore where there is : >> a - no existing semaphore - no ongoing edits. >> b - the semaphore's session is the same - repeat edits by this user >> in the same session (expired or otherwise). >> c - the semaphore has expired - the other user simply took too long. >> >> If the semaphore cannot be set it will be because of : >> d - Different non expired session - someone else is editing the record. >> >> When a user saves the row, you just remove the semaphore. >> >> The semaphores could be in a separate table (rather than on the record >> itself). >> >> Different tables have different number of columns so take different >> amounts of time to edit, so each table would have a different amount >> of time from edit to expiry. >> >> An entry on a lookup table (just a description) should, in the main, >> be completed within 30 seconds. >> >> But a detail line for a purchase order may take several minutes. >> >> You'll have to tune this to your own needs. >> >> > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > A "semaphore" is just a flag. Nothing else. You can implement it in any way you like as long as _ALL_ code related to locking uses the semaphores. A common technique for "locking" files is to create a folder called filename.lck A directory can only exist or not. You try to create the directory. If you did, you got the lock. If not, someone else has. The same approach should be used for DB locking in this manner. You try to place the lock (with the conditions defined in the WHERE clause under which it should succeed). If the lock doesn't get written, then you don't have it. What you _DON'T_ do, is see if the lock is already there before trying to write one. No need and provides the possibility for another user, using the same code, to be interleaved. Also, no need for transactions at this stage too. You put the lock on (if you are allowed to). Now you can edit and re-edit the row until you've finished. This technique is described quite well in http://en.wikipedia.org/wiki/Semaphore_(programming) One of the important aspects to using semaphores is that the process to set (and either succeed or fail) must not be interrupted, hence why you don't try to read the presence of the lock before setting it. I hope that helps some. I used to develop using an old DOS based 4GL called Sage Retrieve 4GL (prior to that it was called Sage Skybase). This uses a modified D-ISAM db structure and semaphores for locking. You'd try to lock a record and process the failure. Quite easy really. By extending this concept to include an expiry time within the lock, you've got your auto-unlock feature written. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Automatic logoff
On 28 January 2010 20:29, listread wrote: > Richard, > > One more question (for now): Are there advantages to using a separate > table for locking, rather than specifying a lock column in the table you > want to lock the row in? > > - Ron > > On 1/28/2010 5:47 AM, Richard Quadling wrote: >> >> On 27 January 2010 17:20, listread wrote: >> >>> >>> Richard, >>> >>> I think I need to learn about semaphores! Any suggestions for a good >>> tutorial? >>> >>> One of the things we want to do is exclude locked records from a query. >>> Will semaphores provide for that? >>> >>> Thanks! >>> >>> - Ron >>> >>> >>> >>> >>> On 1/27/2010 8:14 AM, Richard Quadling wrote: >>> >>>> >>>> The technique I've used in the past is semaphore locking, where the >>>> semaphore contains the session and the expected expiry time. >>>> >>>> Follow this. >>>> >>>> User a starts the process of editing a record. >>>> Set the semaphore where there is : >>>> a - no existing semaphore - no ongoing edits. >>>> b - the semaphore's session is the same - repeat edits by this user >>>> in the same session (expired or otherwise). >>>> c - the semaphore has expired - the other user simply took too long. >>>> >>>> If the semaphore cannot be set it will be because of : >>>> d - Different non expired session - someone else is editing the >>>> record. >>>> >>>> When a user saves the row, you just remove the semaphore. >>>> >>>> The semaphores could be in a separate table (rather than on the record >>>> itself). >>>> >>>> Different tables have different number of columns so take different >>>> amounts of time to edit, so each table would have a different amount >>>> of time from edit to expiry. >>>> >>>> An entry on a lookup table (just a description) should, in the main, >>>> be completed within 30 seconds. >>>> >>>> But a detail line for a purchase order may take several minutes. >>>> >>>> You'll have to tune this to your own needs. >>>> >>>> >>>> >>> >>> -- >>> PHP Database Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >>> >>> >>> >> >> A "semaphore" is just a flag. Nothing else. You can implement it in >> any way you like as long as _ALL_ code related to locking uses the >> semaphores. >> >> A common technique for "locking" files is to create a folder called >> filename.lck >> >> A directory can only exist or not. >> >> You try to create the directory. If you did, you got the lock. If not, >> someone else has. >> >> The same approach should be used for DB locking in this manner. >> >> You try to place the lock (with the conditions defined in the WHERE >> clause under which it should succeed). If the lock doesn't get >> written, then you don't have it. >> >> What you _DON'T_ do, is see if the lock is already there before trying >> to write one. No need and provides the possibility for another user, >> using the same code, to be interleaved. >> >> Also, no need for transactions at this stage too. >> >> You put the lock on (if you are allowed to). Now you can edit and >> re-edit the row until you've finished. >> >> This technique is described quite well in >> http://en.wikipedia.org/wiki/Semaphore_(programming) >> >> One of the important aspects to using semaphores is that the process >> to set (and either succeed or fail) must not be interrupted, hence why >> you don't try to read the presence of the lock before setting it. >> >> I hope that helps some. >> >> I used to develop using an old DOS based 4GL called Sage Retrieve 4GL >> (prior to that it was called Sage Skybase). This uses a modified >> D-ISAM db structure and semaphores for locking. You'd try to lock a >> record and process the failure. Quite easy really. >> >> By extending this concept to include an expiry time within the lock, >> you've got your auto-unlock feature written. >> >> >> > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > I've never done any studies on this. I've always used a separate table. I suppose one reason is that you don't touch the live data unless you have the semaphore. Also, the additional columns would need to be on every table where semaphoring is required. So, having all the locks in a lock table seems right. The lock table should be small, whereas the data it is locking could be very big. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] FW: semaphores WAS: [PHP-DB] Automatic logoff
On 28 January 2010 21:38, Daevid Vincent wrote: > An intersting synopsis of "Semaphores". I've done similar things in the > past, but never knew this is what I was doing. LOL. Just like I've built an > uber XML parser/editor and didn't know that I actually built a "Factory". > Ahhh... good old data structures -- they didn't teach these things when > I was in college (20+ years ago). > > I particularly found this part interesting as I hadn't considered this, > "What you _DON'T_ do, is see if the lock is already there before trying to > write one. No need and provides the possibility for another user, > using the same code, to be interleaved." I am assuming (and correct me if > I'm wrong) as you will get a race condition (on a sufficiently large > system) wherein, two users check "is there a directory lock", and the > system responds "No" to each, and then the code attempts to create a > directory. Then one of them gets a lock granted (i.e a directory) and since > 'there can be only one' [highlander reference] the other one THINKS they > got the lock (too). Doh! What happens in code depends upon the code. If the code doesn't test the result of assigning the lock, then there is no lock. Every write will overwrite whatever was previously written if all users use the same code. And there is the major flaw of distributed or client initiated semaphoring. It is entirely possible for you to open up your DB gui tool and amend the data. Completely bypassing the semaphoring. So, whilst semaphoring is really useful for long edits, it isn't perfect. But as long as all code use the same semaphoring logic, then it is fine. > The wiki page is also interesting and I'd always heard these terms, but > never really knew what they were in a practical sense: "A mutex is a binary > semaphore that usually incorporates extra features, such as ownership, > priority inversion protection or recursivity. The differences between > mutexes and semaphores are operating system dependent, though mutexes are > implemented by specialized and faster routines. Mutexes are meant to be > used for mutual exclusion (post/release operation is restricted to thread > which called pend/acquire) only and binary semaphores are meant to be used > for event notification (post-ability from any thread) and mutual exclusion. > Events are also sometimes called event semaphores and are used for event > notification." > > And this also helped to clarify: > http://stackoverflow.com/questions/62814/difference-between-binary-semaphor > e-and-mutex > Ha! Toilets. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] FW: semaphores WAS: [PHP-DB] Automatic logoff
On 29 January 2010 13:43, listread wrote: > Would this support the idea of putting a lock column in the table to be > locked? If an admin had cause to go into a table with a DB gui tool, at > least he would see the semaphore as a "warning". > > The same table would also simplify code and make the sb more portable. > > As for speed, you have to query that (those) records to be updated anyway at > some point - a special lock table would require another query and if it that > lock table was for ALL the other tables in the system, it would be getting > more hits than any one table. > > This has been very educational for me - thanks for the discussion. > You could easily extend this to be table locking. Assuming that the key is tableid/rowid, then you would need to include logic to handle tableid/null to indicate the whole table. Obviously, you can't put a table lock on if another user has a row or table lock. It does get a little more messy, but perfectly doable in a single SQL query. Where you put the lock is pretty much up to you, but the extra columns have no bearing on the table. They aren't data for that table. A semaphore table would be small. You can easily remove all the locks from all the tables/rows simply by truncating the semaphore table. The other way around all of this is to not do any locking at all. Simply log when a row is saved and when you go to save the row include ... where rows_last_edited_datetime = the_datetime_I_read_when_I_started_editing_the_row But I don't like that method. It is first saves wins, rather than locking. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MIME Alternatimve Emails
On 30 January 2010 13:04, Karl DeSaulniers wrote: > Hi List, > Good morning. Hope all are well on this Saturday. > I am in need of a little assistance. I am creating an HTML email. > I have created it successfully already, so I know the code works for the > HTML part. > My question is about the headers of the message. > > Here is my code at the end before the message is sent off. > > //other code that sets up $html and $text > > if (strtoupper(substr(PHP_OS, 0, 3) == 'WIN')) { > $eol="\r\n"; > } else if (strtoupper(substr(PHP_OS, 0, 3) == 'MAC')) { > $eol="\r"; > } else { > $eol="\n"; > } > > // To send HTML mail, the Content-type header must be set > $headers = 'From: '.$from.$eol; > $headers .= 'To: "'. $username .'" <' . $email . '>'.$eol; > > //$headers .= 'Cc: ' . $from; > //$headers .= 'BCc: ' . $from; > $headers .= 'Reply-To: ' . $from .$eol; > > $headers .= 'X-Mailer: PHP/' . phpversion().$eol; > $headers .= 'X-sender: <' . $from . '>'.$eol; > $headers .= 'X-receiver: <' . $email . '>'.$eol; > > $headers .= 'Message-ID: <'.$MessageID.'@'.$host.'>'.$eol; > $headers .= 'Date: '.$time.$eol; > > // Additional headers > $headers .= 'X-Priority: 1'.$eol; > $headers .= 'X-MSMail-Priority: High'.$eol; > $headers .= 'X-MimeOLE: Generated By Design Drumm - MultiPart/Alt > E-Mail'.$eol; > > $headers .= 'MIME-Version: 1.0' . $eol; > $headers .= 'Content-type: multipart/alternative'; > $headers .= 'boundary="'.$boundary.'"' . $eol.$eol; > > // Make sure there are no bare linefeeds in the headers > //$headers = preg_replace('#(? this code. Can anyone tell me if it works. > > $message = '--'.$boundary.$eol; > $message .= 'Content-type: text/html; charset="UTF-8"'.$eol; > $message .= 'Content-Transfer-Encoding: 8bit'.$eol.$eol; > $message .= $html.$eol.$eol; > > $message .= '--'.$boundary.$eol; > $message .= 'Content-type: text/plain; charset="UTF-8"'.$eol; > $message .= 'Content-Transfer-Encoding: quoted-printable'.$eol.$eol; > > $message .= $text.$eol.$eol; > $message .= '--'.$boundary.'--'.$eol.$eol; > > return mail($email, $subject, $message, $headers); > > } > > My question is what order should the headers be in from top to bottom that I > have to work properly? > Currently it does not display the HTML on MAC but it does in Hotmail. MAC > mail is grabbing the text version. > So my susspicion is that the headers are just wrong. > > It has worked on MAC mail before I tried the MIME boundaries. > > TIA, > > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > I would simplify all of this by using a PHP class specifically covering this issue. I use the html_mime_mail5 class from phpguru.org (now called RMail) at http://www.phpguru.org/static/Rmail Though, you'll probably find PHPMailer is the most commonly used class at http://phpmailer.worxware.com/ -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MIME Alternatimve Emails
On 30 January 2010 13:23, Karl DeSaulniers wrote: > Thanks Richard for your response, > Since the code is a little long, I didn't want to post too much, so > I just put the last part with the headers I was using. > also, since the mailer worked previously, > I was thinking it was just the new header info I was plugging in > so to do the whole checking if a user can receive HTML emails or text. > > Any thoughts on what order I should be putting the headers I have? > TIA, > > Karl > > > On Jan 30, 2010, at 7:13 AM, Richard Quadling wrote: > >> On 30 January 2010 13:04, Karl DeSaulniers wrote: >>> >>> Hi List, >>> Good morning. Hope all are well on this Saturday. >>> I am in need of a little assistance. I am creating an HTML email. >>> I have created it successfully already, so I know the code works for the >>> HTML part. >>> My question is about the headers of the message. >>> >>> Here is my code at the end before the message is sent off. >>> >>> //other code that sets up $html and $text >>> >>> if (strtoupper(substr(PHP_OS, 0, 3) == 'WIN')) { >>> $eol="\r\n"; >>> } else if (strtoupper(substr(PHP_OS, 0, 3) == 'MAC')) { >>> $eol="\r"; >>> } else { >>> $eol="\n"; >>> } >>> >>> // To send HTML mail, the Content-type header must be set >>> $headers = 'From: '.$from.$eol; >>> $headers .= 'To: "'. $username .'" <' . $email . '>'.$eol; >>> >>> //$headers .= 'Cc: ' . $from; >>> //$headers .= 'BCc: ' . $from; >>> $headers .= 'Reply-To: ' . $from .$eol; >>> >>> $headers .= 'X-Mailer: PHP/' . phpversion().$eol; >>> $headers .= 'X-sender: <' . $from . '>'.$eol; >>> $headers .= 'X-receiver: <' . $email . '>'.$eol; >>> >>> $headers .= 'Message-ID: <'.$MessageID.'@'.$host.'>'.$eol; >>> $headers .= 'Date: '.$time.$eol; >>> >>> // Additional headers >>> $headers .= 'X-Priority: 1'.$eol; >>> $headers .= 'X-MSMail-Priority: High'.$eol; >>> $headers .= 'X-MimeOLE: Generated By Design Drumm - MultiPart/Alt >>> E-Mail'.$eol; >>> >>> $headers .= 'MIME-Version: 1.0' . $eol; >>> $headers .= 'Content-type: multipart/alternative'; >>> $headers .= 'boundary="'.$boundary.'"' . $eol.$eol; >>> >>> // Make sure there are no bare linefeeds in the headers >>> //$headers = preg_replace('#(?>> this code. Can anyone tell me if it works. >>> >>> $message = '--'.$boundary.$eol; >>> $message .= 'Content-type: text/html; charset="UTF-8"'.$eol; >>> $message .= 'Content-Transfer-Encoding: 8bit'.$eol.$eol; >>> $message .= $html.$eol.$eol; >>> >>> $message .= '--'.$boundary.$eol; >>> $message .= 'Content-type: text/plain; charset="UTF-8"'.$eol; >>> $message .= 'Content-Transfer-Encoding: quoted-printable'.$eol.$eol; >>> >>> $message .= $text.$eol.$eol; >>> $message .= '--'.$boundary.'--'.$eol.$eol; >>> >>> return mail($email, $subject, $message, $headers); >>> >>> } >>> >>> My question is what order should the headers be in from top to bottom >>> that I >>> have to work properly? >>> Currently it does not display the HTML on MAC but it does in Hotmail. MAC >>> mail is grabbing the text version. >>> So my susspicion is that the headers are just wrong. >>> >>> It has worked on MAC mail before I tried the MIME boundaries. >>> >>> TIA, >>> >>> >>> Karl DeSaulniers >>> Design Drumm >>> http://designdrumm.com >>> >>> >> >> I would simplify all of this by using a PHP class specifically >> covering this issue. >> >> I use the html_mime_mail5 class from phpguru.org (now called RMail) at >> http://www.phpguru.org/static/Rmail >> >> Though, you'll probably find PHPMailer is the most commonly used class >> at http://phpmailer.worxware.com/ >> >> -- >> - >> Richard Quadling >> "Standing on the shoulders of some very clever giants!" >> EE : http://www.experts-exchange.com/M_248814.html >> EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp >> Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 >> ZOPA : http://uk.zopa.com/member/RQuadling >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > I use RMail. With that ... $RMail->addText($TextMessage); $RMail->addHTML($HTMLMessage, $DirForImages); That's it. If the client can only read text, it will show the text. Otherwise it will show the HTML. The alternative headers are all taken care of by the class. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing images
On 3 February 2010 16:07, Karl DeSaulniers wrote: > Thank you all for your numerous responses. > > I hear you loud and clear. I was wanting to see if it would be less of a > burden on the server and secure my images better to put the images inside a > database, but > as you all have almost uniformly stated, this would not be the best > situation. > I currently have all my images referenced by url in my database and stored > in a folder/s and I think I will keep it that way... > > Thanks for your 2 cents, > > :) > > Karl > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If you put the images OUTSIDE of the webroot/docroot/public_html folder (whatever you have), then a user cannot directly navigate to the file. e.g. /home/sites/your_site/public_html/images/image1.jpg http://www.yoursite.com/images/image1.jpg would probably work. But ... /home/sites/your_site/public_html/getImage.php /home/sites/your_site/hidden_images/image1.jpg Now, there is no way I can load image1.jpg from my browser. I have to use getImage.php, which I assume would require me to login or authenticate myself in some way. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing images
On 3 February 2010 16:22, Karl DeSaulniers wrote: > A.. > Very nice. I did not think of that. > But lets say its a whole bunch of images and multiple people may be > accessing them. > Is it safe to have them accessing a directory outside the public_html > directory? > Thanks, > > Karl > > On Feb 3, 2010, at 10:14 AM, Richard Quadling wrote: > >> On 3 February 2010 16:07, Karl DeSaulniers wrote: >>> >>> Thank you all for your numerous responses. >>> >>> I hear you loud and clear. I was wanting to see if it would be less of a >>> burden on the server and secure my images better to put the images inside >>> a >>> database, but >>> as you all have almost uniformly stated, this would not be the best >>> situation. >>> I currently have all my images referenced by url in my database and >>> stored >>> in a folder/s and I think I will keep it that way... >>> >>> Thanks for your 2 cents, >>> >>> :) >>> >>> Karl >>> >>> -- >>> PHP Database Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >>> >>> >> >> If you put the images OUTSIDE of the webroot/docroot/public_html >> folder (whatever you have), then a user cannot directly navigate to >> the file. >> >> e.g. >> /home/sites/your_site/public_html/images/image1.jpg >> >> http://www.yoursite.com/images/image1.jpg would probably work. >> >> But ... >> >> /home/sites/your_site/public_html/getImage.php >> /home/sites/your_site/hidden_images/image1.jpg >> >> Now, there is no way I can load image1.jpg from my browser. I have to >> use getImage.php, which I assume would require me to login or >> authenticate myself in some way. >> >> >> -- >> - >> Richard Quadling >> "Standing on the shoulders of some very clever giants!" >> EE : http://www.experts-exchange.com/M_248814.html >> EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp >> Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 >> ZOPA : http://uk.zopa.com/member/RQuadling > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > If the file is outside of the docroot, then they _CANNOT_ access them. There is no url to the image! So, a script which examines the session to make sure the request is valid is normally enough to restrict feeding the images to valid users. Multiple simultaneous readers are not a problem. If you have any writers, then you need to introduce a locking mechanism or some other protection. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing images
On 3 February 2010 16:35, Karl DeSaulniers wrote: > Any pointers on where I can find a sample locking script. > I may have artists editing files and that would be great to have a > locking of the file while an artist is working on it so no one over writes > each other. > LMK, > Thanks, > > Karl > > On Feb 3, 2010, at 10:31 AM, Richard Quadling wrote: > >> If you have any writers, then you need to introduce a locking >> mechanism or some other protection. > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > Take a read at what started in http://marc.info/?l=php-db&m=126444533212143&w=2 and http://marc.info/?l=php-db&m=126471476932749&w=2 I mention using a simple directory as a lock. You can implement it in any way you want though. If you already have a mechanism to stop multiple users from editing the same image via a DB, then use that, but just store the image outside of the web root. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing Images #2
On 5 February 2010 09:08, elk dolk wrote: >> -- >> > I have my photos in /public_html/img/gid directory and >> with this path: >> > > getImage.php the server displays the photos. >> > >> > Now if I put my photos outside of the public_html like >> this: >> > /hidden_images/img/gid >> > >> > what would be the correct path to the photos in the >> getImage.php script? >> >> Do you mean what url? You'll need a script to pull them >> from outside the document root. The advantage of this is you >> can do authentication checks before displaying the image. >> The disadvantage is the web-server isn't serving the images >> directly so there will be a slow down. >> >> So you point your images to >> >> getimage.php?image=123456 >> > .. > thank you for your useful comment, but I mean what url should I use > for img src instead of src='http://www.mydomain.com/img/{$gid}/{$photoFileName}' in the getImage.php > script? > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > The whole point of putting the images _OUTSIDE_ of the web root is to completely remove the possibility of having all your images downloaded without any checks of who is doing it. If I can enter the URL of the image directly, why would I pay you for it (for example). So, producing a symlink/alias of the images folder so that it DOES exist within docroot is completely redundant. Something like this is what I would expect your getImage.php script to be. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP Objects and SQL Results
On 12 February 2010 23:46, Paul Hollingworth wrote: > Thanks for the code Eric, it seems to loosely provide the functionality that > I'm after. > > Just out of interest though, is there no other way to find the next result > row in an object apart from dumping it into an array? > > Thanks, > Paul You can use mysql_result() (http://docs.php.net/manual/en/function.mysql-result.php) to read a specific row from the result set. But you could also use the SQL WHERE or LIMIT clause to only retrieve the specific row or rows you wanted. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Using mssql serial command in PHP 5.3
On 18 February 2010 06:15, Beo Tse wrote: > I am using the EasyPHP5.3.0 and MS SQL Server Management Studio Express > 9.00.2047.00 > > Config of EasyPHP5.3.0 > == > PHP 5.3.0 > Apache 2.2.13 > MySQL 5.1.37 > PhpMyAdmin 3.2.1 > > I want to use mssql command (e.g. mssql_connect) as my db connector. When I > run my code the following error has prompted. Can someone tells me what I > can do? > > Fatal error: Call to undefined function mssql_connect() in > C:\EasyPHP5.3.0\www\ATIS\dbfun.php on line 70 > > Thank you. > > Best Regards > > Beo Tse > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Normally, you would need extension=php_mssql.dll in your php.ini file. But, php_mssql.dll is not available for 5.3. Instead you would need to either use odbc or the MS SQL Server Driver for PHP (http://www.codeplex.com/SQL2K5PHP). Both of these require an ODBC driver (MS SQL Native Client Driver is recommended as it supports all the most recent DBs). http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c6c3e9ef-ba29-4a43-8d69-a2bed18fe73c contains all the links (there are a lot). Look for "Microsoft SQL Server 2005 Driver for PHP" and "Microsoft SQL Server 2008 Native Client" for more info. The Native Client download for x86 is http://go.microsoft.com/fwlink/?LinkId=123717&clcid=0x409 -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC
On 24 March 2010 16:05, Aaron Paetznick wrote: > Many of my MySQL tables have columns with the same name. I want to have PDO > include table names in named result sets. For example: > > $sth = $dbh->prepare("SELECT * FROM table0, table1"); > $result = $sth->fetchAll(PDO::FETCH_ASSOC); > > > I want $result to be organized like: > > echo $result["table0.column0"]; > echo $result["table0.column1"]; > echo $result["table1.column0"]; > echo $result["table1.column1"]; > > > Or, alternatively: > > echo $result["table0"]["column0"]; > echo $result["table0"]["column1"]; > echo $result["table1"]["column0"]; > echo $result["table1"]["column1"]; > > > Any ideas? Thanks! > > > --Aaron > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > The data coming from the server dictates the column names, not PDO. If you need to identify the names, then use aliases in the SQL. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC
On 24 March 2010 18:04, Aaron Paetznick wrote: > Thanks for the advise. I wanted a more automatic method of prefixing column > names, but I ended up just aliasing them all. > > I know I could always just issue multiple SELECTs, but I wish MySQL would > support this sort of functionality natively. I'd really like to reference > my results as $result["table0"]["column0"] from a single SELECT. > > Anyways, thanks all. > > > --Aaron > > > On 3/24/2010 11:28 AM, Niel Archer wrote: >>> >>> Many of my MySQL tables have columns with the same name. I want to have >>> PDO include table names in named result sets. For example: >>> >>> $sth = $dbh->prepare("SELECT * FROM table0, table1"); >>> $result = $sth->fetchAll(PDO::FETCH_ASSOC); >>> >>> >>> I want $result to be organized like: >>> >>> echo $result["table0.column0"]; >>> echo $result["table0.column1"]; >>> echo $result["table1.column0"]; >>> echo $result["table1.column1"]; >>> >>> >>> Or, alternatively: >>> >>> echo $result["table0"]["column0"]; >>> echo $result["table0"]["column1"]; >>> echo $result["table1"]["column0"]; >>> echo $result["table1"]["column1"]; >>> >>> >>> Any ideas? Thanks! >> >> Sounds like you want to UNION two SELECTs >> http://dev.mysql.com/doc/refman/5.0/en/union.html >> >> (SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...) >> UNION >> (SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...) >> >> >>> --Aaron >>> >>> >>> -- >>> PHP Database Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >> >> -- >> Niel Archer >> niel.archer (at) blueyonder.co.uk >> >> >> > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > I use MS SQL and have used other DBs. I've never seen the table name as part of the column name in a result set on any of them. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] session management
On 22 April 2010 18:56, Vinay Kannan wrote: > Hey Guys, > > I need some help on an effficient session management, right now what I do is > check if the user has loggedin using his username, and create a > SESSION['logged']=1, setting a login flag actually, I am not sure if this is > the best way ? > > What do you guys use for sessions, and which is the best possible way ? > > Thanks, > Vinay > https://code.google.com/p/loginsystem-rd/ This was developed as an easy "drop-in" secure login facility. It may give you some mileage. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB]how to handle exception in php
On 2 May 2010 06:26, Bavithra R wrote: > hi friends > > I am doing a simple student mark details project. > for calculating rank I need to compare the total marks one by one. > To do so i use *for loop.* so atlast while reaching the end of the table it > shows the following warning. > * > Warning*: mysql_result() > [function.mysql-result<http://127.0.0.1/pro/function.mysql-result>]: > Unable to jump to row 18 on MySQL result index on line *50* > > It is because it has no next value to compare.How to use try..catch > exception handling in this.?Or any other suggestion. > Can anybody help me > > --Bavithra > Commonly the while loop looks like ... while ($row = mysql_fetch_assoc($result)) { If you intend to use this for other loops, watch out where the result of the function is False to indicate the end of the iterations. while (False !== ($row = mysql_fetch_assoc($result))) { Whilst this offers nothing extra here, for functions where 0 or "" are valid responses and False is the "get me out of here" response, testing for type and value (!==) is important. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Major Cookie Problem
On 13 May 2010 12:32, Barry Zimmerman wrote: > setcookie('peg', 'YES', 'time()+190', '', 0); Can you try removing the quotes from around the expire parameter. setcookie('peg', 'YES', time()+190, '', 0); Richard -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Email validation
On 28 June 2010 15:42, gmail wrote: > Hello to you all! > > I'm facing a problem with a scrip that can validate a form witch accept only > as input email address. > > I have seen many scripts on the net and were very interesting but I'm not > able to implement those scripts on my need. > > I'm explaining myself better! > > I have a form to collect a User Input Email address, > > After a script to check the syntax of the user email is correct. > > Then check the mx record if really exist. > > At the end if the user input (email) meets all my requirements the pass it > (store) in a DB. > > Please I'll be grateful to all your possible solutions > > Thanks. > > > > How much detail do you want? Validating email addresses goes from the mundane to the extreme. The rules on the EXACT format of an email address are significant. Reading http://en.wikipedia.org/wiki/E-mail_address#RFC_specification will give you a lot of detail. There are MANY regular expressions available, again, covering some aspects of a "valid" email address. Some cover more than others. http://www.regular-expressions.info/email.html has a good explanation regarding email address validation using regular expressions (and is part of the RegexBuddy documentation). Note the last line ... "So even when following official standards, there are still trade-offs to be made. Don't blindly copy regular expressions from online libraries or discussion forums. Always test them on your own data and with your own applications." As for the MX part, PHP has the getmxrr() function for this task - http://docs.php.net/manual/en/function.getmxrr.php There are also a lot of user notes covering email address validation. Richard. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] input statement in php
On 26 July 2010 15:38, Bavithra R wrote: > hi friends.. > > > Is there any equivalent statement in php for "scanf" or "cin" in c or c++? > That is without using html and creating forms. WOW. Completely surprised by the two responses that were given. STDIN is a predefined resource, so no need to fopen()/fclose() it. Hope this helps. Regards, Richard Quadling. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] COUNT() query help
On 13 August 2010 13:43, Ron Piggott wrote: > SELECT `bible_concordance_words`.`reference`, > `bible_concordance_words`.`word`, > COUNT(`bible_concordance_word_reference`.`bible_concordance_words_reference`) > AS occurrences FROM `bible_concordance_words` INNER JOIN > `bible_concordance_word_reference` ON > `bible_concordance_words`.`reference` = > `bible_concordance_word_reference`.`bible_concordance_words_reference` > WHERE `word` LIKE '$letter%' ORDER BY `word` ASC SELECT `bible_concordance_words`.`word`, COUNT(`bible_concordance_word_reference`.`bible_concordance_words_reference`) AS occurrences FROM `bible_concordance_words` INNER JOIN `bible_concordance_word_reference` ON `bible_concordance_words`.`reference` = `bible_concordance_word_reference`.`bible_concordance_words_reference` WHERE `word` LIKE '$letter%' GROUP BY `bible_concordance_words`.`word`, ORDER BY `word` ASC -- Richard Quadling. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Test (getting bounced).
Test. -- Richard Quadling. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP-WIN] Select the specific user data from the database
On 5 September 2010 12:21, nagendra prasad wrote: > Hi Experts, > > I have a mysql database. What I want is that when a user login he can able > to see his entries only, so that he can delete, add or edit his entries > only. I have 2 different tables one for user details and another for actual > entries. Please help me. > > Best, > Guru. > If userA's and userB' data are both in the same table, do or will you have issues with key fields? I don't know what the data is, but you would need to include some element of the user in every unique constraint. Depending upon the data, another option is to have a separate table or database per user. This allows for user permissions to be assigned to the table or database. I've used this mechanism when users data needs to be sync across multiple devices and the device initiating the sync was always the most uptodate. Cloning a table was far easier. Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this syntax is correct?
On 6 September 2010 16:11, Ken Kixmoeller wrote: > 2. As Amit kind of said, no password should be stored in a table (available > on a web server) without encrypting the password information. I'd go further and say "no password should be stored in a table". A hash of the salted password, sure. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT / ORDER BY
On 11 September 2010 07:47, Ron Piggott wrote: > > I wrote the query below to determine the 10 most popular words used: > > SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage, > `bible_concordance_words`.`reference` , `bible_concordance_words`.`word` > FROM `bible_concordance_usage` > INNER JOIN `bible_concordance_words` ON > `bible_concordance_usage`.`bible_concordance_words_reference` = > `bible_concordance_words`.`reference` > GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference` > ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC, > `bible_concordance_usage`.`date_accessed` DESC > LIMIT 10 > > What I don't like about the results is that if 8 words have been used 5 > times then the remaining 2 words the query chooses are from words used 4 > times. The results are in alphabetical order A to Z for the words used 5 > times and back to A to Z for words used 4 times. > > My question: is there a way to make my query above into a "sub query" and > have a main query order the results of the sub query "ORDER BY words ASC" > so all the words displayed are in alphabetical order? > > Ron > > Ron > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Can't you just swap the order of the first 2 columns in the ORDER BY clause? e.g. ORDER BY Name, Age will list all the names alphabetically and where there are more than 1 occurrence of a name in the result set, these would be ordered by age. versus. ORDER BY Age, Name will list all the babies in alphabetical order, followed by the toddlers, children, teenagers, adults, grannies and granddads. So, ORDER BY `bible_concordance_words`.`word` ASC, word_usage DESC,`bible_concordance_usage`.`date_accessed` DESC And as you are grouping by `bible_concordance_words`.`word`, there really is never going to be a duplicate. So, there is no need to order by anything else. So, ORDER BY `bible_concordance_words`.`word` ASC is all you should need. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] formatting a word doc using php ?
On 17 September 2010 21:41, Vinay Kannan wrote: > Hello Xperts, > > I am trying out a couple of things and have come across this requirement, > where in a php script should create a doc file with specific formatting, I > can create the file, havent tried it yet, but shouldnt be a problem I guess, > but how do I format the doc file is the question, google search didnt give > good results too :( So not even sure if it can be done actually. > > Thanks, > Vinay > Depending upon the version of document you want to create you have 3 mechanisms available. 1 : To use the older style doc format which is a proprietary binary format controlled by Microsoft and has been reversed engineered (to a degree) by various developers and you can find many instances of php/word tools. 2 : To use the newer style xml format which is fully human readable. 3 : Use PHP's COM interface to interact with an instance of MS Word. Each has its merits and really depends upon how complex you want to go. For example, option 3 allows you to do everything that you can do manually. If you can record a macro doing it in Word, then you can script it in PHP. Learning a little bit about TypeLibraries and reading the VBA documentation for Word will certainly help you there. The main downside here is you need to have a license for Word and to be using Windows. I use PHP's COM for interacting with Crystal Reports Developer Edition. I've not built a report from scratch using PHP, but that is available to me. Just like it is with MS Word. Using a template, you can easily use PHP's COM to talk to Word, create a new document from the template, do search and replace of bookmarks with text and finally save the document. Below is an example of reading a word count from a document called Z:\5words.doc. Visible = 0; $o_Doc = $o_Word->Documents->Open('z:/5words.doc'); echo 'There are ', $o_Doc->BuiltInDocumentProperties(wdPropertyWords), ' word(s) in this document.'; $o_Doc->Close(False); $o_Word->Quit(); unset($o_Doc); unset($o_Word); ?> Using Option 2 means learning how MS have defined their document. Styling, etc. isn't like it is in old style HTML (bold), but more like (but not exactly) using CSS tags (bold). Using Option 1 means you will be limited to whatever has been reverse engineered. And when the binary format changes (though less likely now due to the XML route), then you'd have to be waiting on the developer to fix the code first. If XML is in your capability, then I'd go with that at a first attempt, then the third party class and finally the COM. Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] session expiration
On 28 September 2010 03:06, Ron wrote: > Hi, > > i would like to ask how to set the session expiration. > > on my site when a user logs in, i assign it to $_SESSION['username'] > > and on each page i check if (isset($_SESSION['username']) if not i redirect > it back to login page. > > my problem is it seems like it expires very fast when there's no activity, > how can i make it in such a way it won't expire unless the user logs out? > > regards > Ron On each page do you have session_start(); You can extend the session duration using session_cache_expire(). Make sure you call session_cache_expire() before you call session_start(). -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Books
On 18 October 2010 15:06, Ethan Rosenberg wrote: > Dear List - > > I posted part of this question on the general list. I am posting here just > in case this list did not see the post. This should NOT be taken as any > type of negative reference to the general list, the members of which have > furnished me excellent help. > > Question: > > What books would your recommend to learn HTML/CSS, MySQL and PHP? The books > should start from the basic level and proceed to the intermediate/advanced > level. > > Thanks. > > Ethan Learning HTML/CSS, MySQL and PHP ... On my desk I have php|Architect's Guide to PHP Design Patterns, php|Architect's Zend PHP 5 Certification Guide, Microsoft's Code Complete, The Gang of 4 and Jan Goyvaerts Regular Expression Cookbook. For my HTML and CSS, I use online references and validators - most of the time I build small chunks and use them as templates. I use MS SQL, so I have MS BOL (Books OnLine). My JS work is done mainly using PrototypeJS, so there manual is online. And of course, there is the PHP manual at docs.php.net. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Parsing Tab Delimited file
On 26 October 2010 14:52, Ethan Rosenberg wrote: > At 02:56 PM 10/24/2010, Andrés G. Montañez wrote: >> >> Hi Ethan, >> >> fist you need to read the file, you could use the file() function >> which reads the file as an array, being each line an element; >> the you have to navigate the array and exploding the lines into an array: >> >> $data = file('myfile.txt'); >> >> foreach ($data AS $row) { >> $row = explode("\n", trim($row)); >> var_dump($row); >> } >> >> And there you go. >> >> Cheers. >> >> On 24 October 2010 16:46, Ethan Rosenberg wrote: >> > Dear list - >> > >> > Thanks for all your help. >> > >> > I have a tab delimited file which I wish to import into a data base.  >> > In >> > MySQL I can use the LOAD DATA command.  As far as I know, that command >> > cannot be used in PHP as a mysqli_query.  As I understand, I have to >> > parse >> > the file and use the INSERT command. >> > >> > How do I do it?  Code samples, please. >> > >> > Thanks. >> > >> > Ethan >> > >> > >> > >> > -- >> > PHP Database Mailing List (http://www.php.net/) >> > To unsubscribe, visit: http://www.php.net/unsub.php >> > >> > >> >> >> >> -- >> Andrés G. Montañez >> Zend Certified Engineer >> Montevideo - Uruguay > > > Andres - > > Sorry for the delay. > > Thanks. With what you and Jayson sent, it works beautifully. > > Ethan > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If you need to break the tab file apart then fgetcsv() is probably going to help. Just use "\t" for the $delimiter. Pretty much. But that's assuming you aren't using a mysql tool to import the data directly. (I do a BULK INSERT in MS SQL). -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Incrementing Primary Key
On 27 October 2010 14:11, Ethan Rosenberg wrote: > Dear List - > > Thanks for all your excellent help. > > I am setting up a database for medical research, which will be conducted at > various sites. The sites will be identified by a letter {A,B,C }. The > medical record number [primary key] will start at 1001 and increment by > one(1) for each patient at each site; ie, A 1001, A1002, B1001, B1002 .. > How do I do this? > > Do I need a separate database for each site? > > Ethan I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. "Just doing my job, sir!" Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: ezmlm warning
On 5 November 2010 16:14, Daniel Brown wrote: > On Fri, Nov 5, 2010 at 08:13, Lester Caine wrote: >> Karl DeSaulniers wrote: >>> >>> Hi, Can anyone admin please explain this to me? >> >> If the email server gets bounce messages back for emails set out, then it >> tries to check those emails. If the warning message gets through then >> nothing really happens, but if that bounces as well, then it will disable >> sending more emails to that address. >> I had one for this list this morning as well ... > > Mostly everyone on the list would've gotten that same one. It's > nothing of which to be concerned, it just means that your local > mailservers did what they should: they bounced a poor attempt at a > phishing message claiming to be from the "lists.php.net support team" > (there is no such group). When your mailserver rightfully bounced the > email, ezmlm sent the notice to you that it bounced. > > You can check the content of bounced messages yourself by > following the instructions in any given bounce message you receive > from us. Inside the email, you'll see a message number (not to be > confused with a message ID), and you can build a dynamic email address > to which you simply send a blank email. Moments later, you should > receive a copy of the original email --- and usually that will come > through, by request. > Or you can use the news.php.net site to read the message. http://news.php.net/php.db/47430 -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL values
On 4 December 2010 11:40, Amit Tandon wrote: > Dear Ron > > Or try this > > SELECT * FROM `paypal_payment_info` WHERE ifnull(os1, '') <> > 'commission_paid' > > regds > amit > > "The difference between fiction and reality? Fiction has to make sense." > > > On Sat, Dec 4, 2010 at 7:55 AM, Ron Piggott > wrote: > >> >> When I do the following query in mySQL only 1 record is retrieved. >> >> SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_paid' >> >> I am surprised by this. This one record has no characters in it, but the >> “INSERT INTO” that created it used: ( `os1` ) VALUES ( ‘’ ) instead of: ( >> `os1` ) VALUES ( NULL ) . There are a number of records where `os1` is >> NULL. I would like these rows to retrieve as well. How do I make a WHERE >> clause for a cell that is NULL ? >> >> Ron >> >> The Verse of the Day >> “Encouragement from God’s Word” >> http://www.TheVerseOfTheDay.info >> > http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull where isnull(column, '') <> 'value' -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL to 0 result
On 14 December 2010 21:53, Ron Piggott wrote: > > What change is needed to this query so if “currently_in_rss” is NULL it will > be assigned a value of 0 > > SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM > `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` > > Ron Doesn't that query give you an error saying that `reference` isn't in the GROUP BY clause? I use MS SQL, so the wording my be different but it would be along the lines of ... For the SQL statement : SELECT POH_Contract, COUNT(POH_Contract) FROM [BV-CLUSTER-SQL].Contracts.dbo.POP_Header WHERE POH_Status = 1 GROUP BY POH_Status Column 'bv-cluster-sql.contracts.dbo.pop_header.POH_CONTRACT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. So, fixing the query ... SELECT POH_Contract, COUNT(POH_Contract) FROM [BV-CLUSTER-SQL].Contracts.dbo.POP_Header WHERE POH_Status = 1 GROUP BY POH_Status, POH_Contract now works. So, your query may need to be SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include`, `reference` Normally COUNT() will count NULLs, but as you are grouping by the counted column, nulls would be in their own row. Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Problems w/ goto
On 17 December 2010 17:08, Steve Staples wrote: > On Fri, 2010-12-17 at 10:50 -0600, Jay Blanchard wrote: >> [snip] >> Thank you with your excellent help in the past. Here is another >> puzzler >> >> I am trying to write a program that can have two(2) independent forms >> in one PHP file. When I run the code below [from PHP - A Beginner's >> Guide], to which I have added a second form, it freezes. Without the >> goto statements, it runs. When it does run, it displays both forms >> on one Web screen. What I desire is for the first form to be >> displayed, the data entered and then the second form displayed. In >> an actual, not test program like this one, the data in the second >> form would be dependent on the first form. >> >> What did I do wrong? >> [/snip] >> >> You used GOTO. >> >> In this case I would recommend using something like jQuery to 'hide' one >> form until the other form is complete. PHP has sent the output to the >> browser already, both forms are there and display when you remove the >> GOTO. >> >> GOTO should never be used like this. >> >> GOTO should never be used. >> > > Wow... that brought me back to 1990... using basic and batch files... > I honestly didn't even know that the GOTO was still in existence, > especially within PHP. > > I had to show the people in my office, and we all got a chuckle from teh > XKCD comic in the PHP documentation for GOTO > http://ca2.php.net/goto > > Steve > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > And have you seen all the sad faces ... : { on http://docs.php.net/manual/en/control-structures.goto.php#92763 Can't be good for them. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Getting Results
On 21 January 2011 11:58, Karl DeSaulniers wrote: > I did not know you could do a SELECT inside a SELECT. Commonly known as a "sub select". You can use them like ... SELECT columns FROM ( SELECT columns FROM table) WHERE column IN (SELECT column FROM table) As part of a FROM or as part of a WHERE ... IN clause. They are the probably the most common ones. You also have (depending upon your SQL engine and version) something called common table expressions. (From MS SQL Books Online) ... USE AdventureWorks; GO WITH DirReps(ManagerID, DirectReports) AS ( SELECT ManagerID, COUNT(*) FROM HumanResources.Employee AS e WHERE ManagerID IS NOT NULL GROUP BY ManagerID ) SELECT ManagerID, DirectReports FROM DirReps ORDER BY ManagerID; GO CTE's are great for recursive queries, once you get your head around them. I also use them to help me find the next and previous row to the current row in a result set, where a single table is essentially bound 3 times, but with the CTE, additional optimization seems to be in play and work a LOT faster overall. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Getting Results
On 21 January 2011 11:58, Karl DeSaulniers wrote: > So to do descending order I'd put DES or DEC? Just curious about that one. ASC and DESC -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Getting Results
On 21 January 2011 12:39, Karl DeSaulniers wrote: > Very informative, > Thank you Richard. > > I will have to flag this one and come back to it. > My database structure may require something of this measure down the line > with the auditing ability I plan on building into the admin section. > > Never heard of USE AdventureWorks. Is that a SQL system function? > Or just a table in the examples database? USE [1] is a Transact SQL statement (MS call their SQL language T-SQL). AdventureWorks is one of the demo DBs MS supply to help people learn about using their server. Regards, Richard. [1] http://msdn.microsoft.com/en-us/library/ms188366.aspx -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] 2nd Pair of eyes please
On 26 January 2011 23:52, Donovan Brooke wrote: > [snip] >> >> print = ""; > > ^ > Never mind, found it. :-/ > > Donovan > > > -- > D Brooke > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Considering that the error is a parse error, I'm guessing you've turned off all your error reporting. If you are in development, then I'd turn on ALL the errors and warnings. error_reporting=-1 display_startup_errors=1 display_errors=1 at least. Look through php.ini-development to see what choices you have and decide what would be useful. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Error while inserting data into mysql
On 1 March 2011 10:25, nagendra prasad wrote: > Hi All, > > I have 2 simple php pages. One with the form just like some sales voucher. > Another is to print in a proper format and to insert the form database into > mysql. When I enter some data in the sales voucher everything is going well, > however when it comes to the second php for inserting data into mysql I am > getting an error like "Error: Unknown column 'scloth100_qty' in 'field > list'". I have checked everything but not sure about the error. Please help > me with it. > > Best, > Guru. > You have some PHP code. It is attempting to write to a mysql table. One of the columns you've coded for doesn't exist. Maybe. Can you show us the table structure and the exact SQL statement you are executing please? Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP-WIN] Re: Error while inserting data into mysql
On 1 March 2011 10:59, nagendra prasad wrote: > Hi All, > > I have attached the table structure and the PHP code. > > Best, > Guru. > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > Thank you for that. Can you tell me what column in the table is scloth100_qty? I can't find it. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP-WIN] Re: Error while inserting data into mysql
On 1 March 2011 10:59, nagendra prasad wrote: > Hi All, > > I have attached the table structure and the PHP code. > > Best, > Guru. > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > The scloth100 columns I can see are ... |scloth100_totamt|int(20)|Yes|NULL |scloth100_utp|int(20)|Yes|NULL |scloth100_val|int(20)|Yes|NULL |scloth100_vatp|int(20)|Yes|NULL |scloth100_vatr|int(10)|Yes|NULL |scloth100|varchar(100)|Yes|NULL No scloth100_qty. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE IF
On 14 March 2011 15:42, Gary wrote: > I have a table in a mysql db that has 3 columns that I want to insert > (update) data if the row&column is empty (IS NULL). However I want to start > at the first column, if this column is not null, then move to insert the > data into column 2, if that is empty, then insert data into column 3. > > I have been looking over the manual and boards but am not finding a > solution. > > Can anyone point me in the right direction. > > -- > Gary > > > > __ Information from ESET Smart Security, version of virus signature > database 5952 (20110314) __ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > You would have to code it. I'm not a mysql guru (MSSQL is what I use), but ... UPDATE Table SET Column3 = CASE WHEN Column3 IS NULL AND Column2 IS NOT NULL AND Column1 IS NOT NULL THEN Value ELSE Column3 END, Column2 = CASE WHEN Column2 IS NULL AND Column1 IS NOT NULL THEN Value ELSE Column2 END, Column1 = CASE WHEN Column1 IS NULL THEN Value ELSE Column1 END WHERE IDColumn = ID something like that? -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Stored Procedures, Dynamic SQL or static SQL and testing.
Hi. What preferences do you all have when using SQL. Stored Procedures or PHP generated dynamic SQL or parameterized static SQL. Whichever you use how do you test them? Do you use unit testing in your PHP code? If so, do you also have unit code testing for the SQL statements (I suppose this mainly relates to SPs). Does using a different RDBMS make a difference to the SQL coding style you'd use? This isn't for any sort of thesis, just trying to see what the general consensus is of those that reply. Thanks for answering. Richard Quadling. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP-WIN] Need Help with PHP Mails
2011/4/4 Guru™ : > Hi All, > > I have this HTML form > > > > <!-- > .style14 {font-size: 24px > } > .style14 {font-size: 12px} > --> > > > > > cellpadding="0" cellspacing="0" bordercolor="#00"> > > align="right">Your Name > type="text" size="50" /> > > > Contact > Numbers > > > (please specify STD code) > > > align="right">Email > size="50" /> > > > > > > > > > > > > > > What I want is when the user click on the submit button the data from this > form should directly comes to my email. I tried mail function but not > working for me. I just need these 3 fields in my mail. Please help me with > this. > > -- > *Best, > * > *Guru™* > As this is a PHP for Windows mailing list, I assume you actually are having a problem with PHP on Windows? And not your HTML code. As you are in a PHP for Windows mailing list, I assume you are on Windows, which, as you are now experiencing, doesn't have a mail server built in, nor an app you can run that provides the same facility. See http://uk.php.net/manual/en/book.mail.php for all the lovely documentation, especially with the runtime configuration options. Most likely, you've not set these appropriately. But, in short, you are going to need to tell us what is NOT working for any realistic help. Personally, I use HTMLMimeMail5 (now called RMail) from phpguru.com. PHPMailer is another tool that does a similar job. It may be better, I've just never used it. Regards, Richard. P.S. "Guru"? Really? -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP-WIN] Need Help with PHP Mails
2011/4/4 Guru™ : > Hi Richard, > I am using simple PHP mail function but, I am unable to send an email to > myself. What I want is when the user click on submit button I should get an > email with the form data. > > > > 2011/4/4 Richard Quadling >> >> 2011/4/4 Guru™ : >> > Hi All, >> > >> > I have this HTML form >> > >> > >> > >> > <!-- >> > .style14 {font-size: 24px >> > } >> > .style14 {font-size: 12px} >> > --> >> > >> > >> > >> > >> > > > cellpadding="0" cellspacing="0" bordercolor="#00"> >> > >> > > > align="right">Your Name >> > > > type="text" size="50" /> >> > >> > >> > Contact >> > Numbers >> > >> > >> > (please specify STD >> > code) >> > >> > >> > > > align="right">Email >> > > > size="50" /> >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > What I want is when the user click on the submit button the data from >> > this >> > form should directly comes to my email. I tried mail function but not >> > working for me. I just need these 3 fields in my mail. Please help me >> > with >> > this. >> > >> > -- >> > *Best, >> > * >> > *Guru™* >> > >> >> As this is a PHP for Windows mailing list, I assume you actually are >> having a problem with PHP on Windows? >> >> And not your HTML code. >> >> As you are in a PHP for Windows mailing list, I assume you are on >> Windows, which, as you are now experiencing, doesn't have a mail >> server built in, nor an app you can run that provides the same >> facility. >> >> See http://uk.php.net/manual/en/book.mail.php for all the lovely >> documentation, especially with the runtime configuration options. >> >> Most likely, you've not set these appropriately. >> >> But, in short, you are going to need to tell us what is NOT working >> for any realistic help. >> >> Personally, I use HTMLMimeMail5 (now called RMail) from phpguru.com. >> PHPMailer is another tool that does a similar job. It may be better, >> I've just never used it. >> >> Regards, >> >> Richard. >> >> P.S. "Guru"? Really? >> -- >> Richard Quadling >> Twitter : EE : Zend >> @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY > > > > -- > Best, > Guru™ > > So, just like I said, you haven't setup PHP's mail configuration options and/or you don't have a local SMTP server. Without these being correct, you won't get any email. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP-WIN] Need Help with PHP Mails
2011/4/4 Guru™ : > Hi, > > Here is the PHP script I am using to send email to myself. > > > > $party_name = $HTTP_POST_VARS['party_name']; > $contact_no = $HTTP_POST_VARS['contact_no']; > $email = 'nagendra802...@gmail.com'; > > > mail($email,$party_name,$contact_no); > > ?> > > > > 2011/4/4 Richard Quadling >> >> 2011/4/4 Guru™ : >> > Hi Richard, >> > I am using simple PHP mail function but, I am unable to send an email to >> > myself. What I want is when the user click on submit button I should get >> > an >> > email with the form data. >> > >> > >> > >> > 2011/4/4 Richard Quadling >> >> >> >> 2011/4/4 Guru™ : >> >> > Hi All, >> >> > >> >> > I have this HTML form >> >> > >> >> > >> >> > >> >> > <!-- >> >> > .style14 {font-size: 24px >> >> > } >> >> > .style14 {font-size: 12px} >> >> > --> >> >> > >> >> > >> >> > >> >> > >> >> > > >> > cellpadding="0" cellspacing="0" bordercolor="#00"> >> >> > >> >> > > >> > align="right">Your Name >> >> > > >> > type="text" size="50" /> >> >> > >> >> > >> >> > Contact >> >> > Numbers >> >> > >> >> > >> >> > (please specify STD >> >> > code) >> >> > >> >> > >> >> > > >> > align="right">Email >> >> > > >> > type="text" >> >> > size="50" /> >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > What I want is when the user click on the submit button the data from >> >> > this >> >> > form should directly comes to my email. I tried mail function but not >> >> > working for me. I just need these 3 fields in my mail. Please help me >> >> > with >> >> > this. >> >> > >> >> > -- >> >> > *Best, >> >> > * >> >> > *Guru™* >> >> > >> >> >> >> As this is a PHP for Windows mailing list, I assume you actually are >> >> having a problem with PHP on Windows? >> >> >> >> And not your HTML code. >> >> >> >> As you are in a PHP for Windows mailing list, I assume you are on >> >> Windows, which, as you are now experiencing, doesn't have a mail >> >> server built in, nor an app you can run that provides the same >> >> facility. >> >> >> >> See http://uk.php.net/manual/en/book.mail.php for all the lovely >> >> documentation, especially with the runtime configuration options. >> >> >> >> Most likely, you've not set these appropriately. >> >> >> >> But, in short, you are going to need to tell us what is NOT working >> >> for any realistic help. >> >> >> >> Personally, I use HTMLMimeMail5 (now called RMail) from phpguru.com. >> >> PHPMailer is another tool that does a similar job. It may be better, >> >> I've just never used it. >> >> >> >> Regards, >> >> >> >> Richard. >> >> >> >> P.S. "Guru"? Really? >> >> -- >> >> Richard Quadling >> >> Twitter : EE : Zend >> >> @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY >> > >> > >> > >> > -- >> > Best, >> > Guru™ >> > >> > >> >> So, just like I said, you haven't setup PHP's mail configuration >> options and/or you don't have a local SMTP server. >> >> Without these being correct, you won't get any email. >> >> >> >> >> -- >> Richard Quadling >> Twitter : EE : Zend >> @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY > > > > -- > Best, > Guru™ > > That's all well and good. But ... and this is pretty much me repeating myself, what about your mail configuration. Please read http://uk.php.net/manual/en/mail.configuration.php and tell us what you've got set. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Pause While-loop when waiting for function call to complete
On 24 April 2011 05:19, listread wrote: > While listing each row in a MySQL database, I need to call a function or > another php file for each row. > > How can I make the while-loop wait for the called function to complete > before continuing to the next row in the while-loop? > > Thanks! > > - Ron > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > You don't need to. PHP executes the code in order. When the code makes a call to a function, that function will need to complete before the code that follows the call continues. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Converting from PHP4 to php5
On 1 June 2011 14:26, HallMarc Websites wrote: > Without looking at the rules (Sry barely have time to reply!) I don't think > this is appropriate for this list. I am fairly certain it is against the > rules to ask/offer work on here. > Can anyone confirm or deny? > > Thank you, > Marc Hall > HallMarc Websites > 610.446.3346 > http://www.hallmarcwebsites.com/ But advertising your services is OK? -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Converting from PHP4 to php5
On 1 June 2011 13:52, Dr Vijay Kumar wrote: > i AM WONDERING IF SOMEONE CAN TAKE UP CONVERSION OF MY SOFTWARE FROM PHP4 TO > PHP5 Whilst there will be many aspects of the code that will run quite happily, there are several features that have been deprecated and/or removed, depending upon the version of PHP4 and PHP5 you have and want. There are, I think, 2 valid approaches to this sort of task. 1 - Get the code base functional with PHP5, but without using any of the new features. This involves handling/converting any code that used deprecated and/or removed features of PHP4. Amongst these, register_globals, magic_quotes and ereg would probably be the most popular to look at. This _SHOULD_ be fairly easy to do. Obviously, the complexity and structure of the current code will dictate the amount of time. If, for example, the code isn't documented and there are no testsuites, then a new developer is only going to be able to handle what the code actually does, and not necessarily what the code is supposed to do. Checking the code's behaviour without a testsuite could be pretty difficult. 2 - Refactor the modules of the application, taking into account new features of PHP5. This could involve moving from PHP4 procedural code to PHP5 OOP. This could require a significant investment in time (and therefore money), depending upon the project. And may not be necessary. Richard. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] problem in connecting to mysql from php
On 13 June 2011 13:26, C0mf0rtably Numb <08.kus...@gmail.com> wrote: > Okay. I tried using that too. If I run this: > $link = mysql_connect('localhost', 'root', > > 'Password123'); > if (!$link) { > die('Could not connect: ' . mysql_error()); > } > echo 'Connected successfully'; > mysql_close($link); > ?> > > I get the error: *Fatal error*: Call to undefined function mysql_connect() > in *C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\a.php* on > line *2 > > *I think there is something wrong with my configuration. > > On Mon, Jun 13, 2011 at 6:00 PM, mrfroasty wrote: > >> Your error message is about class not found.That means there is no class >> named "MySQLi".Please use a textbook with online manual >> http://www.php.net/manual/en/ref.mysql.php >> >> On 06/13/2011 02:08 PM, C0mf0rtably Numb wrote: >> > Hello everyone, >> > >> > I am in the process of learning php and I was trying to connect to a >> mysql >> > database on my own computer(localhost). I have done the following as >> > prerequisites: >> > >> > copied the dll files in system32 >> > removed the semicolon(;) from extension=php_mysqli.dll >> > >> > In spite of doing the above when I try to run the following : >> > >> > > > $db = new MySQLi('localhost', 'root', 'Password123', 'test'); >> > $sql = 'SELECT * FROM a123'; >> > $result = $db->query($sql); >> > >> > while($row = $result->fetch_object()) { >> > echo '' . $row->name . ''; >> > } >> > >> > $db->close(); >> > ?> >> > >> > I get the error: *Fatal error*: Class 'MySQLi' not found in *C:\Program >> > Files\Apache Software Foundation\Apache2.2\htdocs\a.php* on line *2 >> > >> > *Please guide me as to how can I get rid of this error? >> > >> > Regards, >> > Kushal >> > >> >> >> -- >> Extra details: >> OSS:Gentoo Linux >> profile:x86 >> Hardware:msi geforce 8600GT asus p5k-se >> location:/home/muhsin >> language(s):C/C++,PHP,SQL,HTML >> Typo:40WPM >> url:http://www.mzalendo.net >> url:http://www.zanbytes.com >> >> >> >> > Copying the files to windows\system ... what files? Where were you told to do this? Normally the extension files you need for PHP are in C:\PHP5\ext (or PHPx). I've never copied a single file into my Windows folder for PHP. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: problem in connecting to mysql from php
On 13 June 2011 14:12, C0mf0rtably Numb <08.kus...@gmail.com> wrote: > Any help? > > On Mon, Jun 13, 2011 at 5:38 PM, C0mf0rtably Numb <08.kus...@gmail.com>wrote: > >> Hello everyone, >> >> I am in the process of learning php and I was trying to connect to a mysql >> database on my own computer(localhost). I have done the following as >> prerequisites: >> >> copied the dll files in system32 >> removed the semicolon(;) from extension=php_mysqli.dll >> >> In spite of doing the above when I try to run the following : >> >> > $db = new MySQLi('localhost', 'root', 'Password123', 'test'); >> $sql = 'SELECT * FROM a123'; >> $result = $db->query($sql); >> >> while($row = $result->fetch_object()) { >> echo '' . $row->name . ''; >> } >> >> $db->close(); >> ?> >> >> I get the error: *Fatal error*: Class 'MySQLi' not found in *C:\Program >> Files\Apache Software Foundation\Apache2.2\htdocs\a.php* on line *2 >> >> *Please guide me as to how can I get rid of this error? >> >> Regards, >> Kushal >> > If you are able to reset your system and then read through the manual Windows installation documentation at http://uk.php.net/manual/en/install.windows.manual.php Personally, I'd change the path to C:\PHP5,x,y, where the x and y are the complete version numbers (PHP5.3.6) , but that's me. I then use Junction to create C:\PHP5 which maps to C:\PHP5.3.6. Junction is like a link at least in terms of how I use it. I'd also take a good look at http://uk.php.net/manual/en/install.windows.commandline.php and http://uk.php.net/manual/en/install.windows.apache2.php One covers making better use of PHP from the command line and the other deals with getting things working with Apache. And an issue. Please make sure you use the right version of PHP for the version of Apache you are using. If you are using a VC9 build Apache (say from ApacheLounge or something like that), then you can use a VC9 build of PHP. If you are using an VC6 build, then you must use a VC6 build of PHP and that limits you to PHP5.2 (PHP5.2.17 is the latest there). I use IIS with FastCGI so I use a NTS (non thread safe) build. I don't know which sort of threading model you need for Apache (hopefully it is mentioned in one of the Apache related pages). And with regard to the php.ini file, you can make it available by many different means. I use the registry. This is documented at http://uk.php.net/manual/en/configuration.file.php Look at ... "As of PHP 5.2.0, the location of the php.ini file can be set for different versions of PHP. The following registry keys are examined in order: [HKEY_LOCAL_MACHINE\SOFTWARE\PHP\x.y.z], [HKEY_LOCAL_MACHINE\SOFTWARE\PHP\x.y] and [HKEY_LOCAL_MACHINE\SOFTWARE\PHP\x], where x, y and z mean the PHP major, minor and release versions. If there is a value for IniFilePath in these keys, then the first one found will be used as the location of the php.ini (Windows only). [HKEY_LOCAL_MACHINE\SOFTWARE\PHP], value of IniFilePath (Windows only)." And, I think finally, if you need different php.ini files for apache and the command line, ... "If php-SAPI.ini exists (where SAPI is used SAPI, so the filename is e.g. php-cli.ini or php-apache.ini), it's used instead of php.ini. SAPI name can be determined by php_sapi_name()." Richard. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: problem in connecting to mysql from php
On 13 June 2011 15:28, C0mf0rtably Numb <08.kus...@gmail.com> wrote: > My php and apache are working fine together. I was doing good with php and > apache until I decided to work with mysql. I will have a look at all the > links you provided and see if gives me a solution. Thanks. > > On Mon, Jun 13, 2011 at 7:36 PM, Richard Quadling > wrote: >> >> On 13 June 2011 14:12, C0mf0rtably Numb <08.kus...@gmail.com> wrote: >> > Any help? >> > >> > On Mon, Jun 13, 2011 at 5:38 PM, C0mf0rtably Numb >> > <08.kus...@gmail.com>wrote: >> > >> >> Hello everyone, >> >> >> >> I am in the process of learning php and I was trying to connect to a >> >> mysql >> >> database on my own computer(localhost). I have done the following as >> >> prerequisites: >> >> >> >> copied the dll files in system32 >> >> removed the semicolon(;) from extension=php_mysqli.dll >> >> >> >> In spite of doing the above when I try to run the following : >> >> >> >> > >> $db = new MySQLi('localhost', 'root', 'Password123', 'test'); >> >> $sql = 'SELECT * FROM a123'; >> >> $result = $db->query($sql); >> >> >> >> while($row = $result->fetch_object()) { >> >> echo '' . $row->name . ''; >> >> } >> >> >> >> $db->close(); >> >> ?> >> >> >> >> I get the error: *Fatal error*: Class 'MySQLi' not found in *C:\Program >> >> Files\Apache Software Foundation\Apache2.2\htdocs\a.php* on line *2 >> >> >> >> *Please guide me as to how can I get rid of this error? >> >> >> >> Regards, >> >> Kushal >> >> >> > >> >> If you are able to reset your system and then read through the manual >> Windows installation documentation at >> http://uk.php.net/manual/en/install.windows.manual.php >> >> Personally, I'd change the path to C:\PHP5,x,y, where the x and y are >> the complete version numbers (PHP5.3.6) , but that's me. I then use >> Junction to create C:\PHP5 which maps to C:\PHP5.3.6. >> >> Junction is like a link at least in terms of how I use it. >> >> I'd also take a good look at >> http://uk.php.net/manual/en/install.windows.commandline.php and >> http://uk.php.net/manual/en/install.windows.apache2.php >> >> One covers making better use of PHP from the command line and the >> other deals with getting things working with Apache. >> >> And an issue. Please make sure you use the right version of PHP for >> the version of Apache you are using. If you are using a VC9 build >> Apache (say from ApacheLounge or something like that), then you can >> use a VC9 build of PHP. >> >> If you are using an VC6 build, then you must use a VC6 build of PHP >> and that limits you to PHP5.2 (PHP5.2.17 is the latest there). I use >> IIS with FastCGI so I use a NTS (non thread safe) build. I don't know >> which sort of threading model you need for Apache (hopefully it is >> mentioned in one of the Apache related pages). >> >> And with regard to the php.ini file, you can make it available by many >> different means. >> >> I use the registry. This is documented at >> http://uk.php.net/manual/en/configuration.file.php >> >> Look at ... >> >> "As of PHP 5.2.0, the location of the php.ini file can be set for >> different versions of PHP. The following registry keys are examined in >> order: [HKEY_LOCAL_MACHINE\SOFTWARE\PHP\x.y.z], >> [HKEY_LOCAL_MACHINE\SOFTWARE\PHP\x.y] and >> [HKEY_LOCAL_MACHINE\SOFTWARE\PHP\x], where x, y and z mean the PHP >> major, minor and release versions. If there is a value for IniFilePath >> in these keys, then the first one found will be used as the location >> of the php.ini (Windows only). >> >> [HKEY_LOCAL_MACHINE\SOFTWARE\PHP], value of IniFilePath (Windows only)." >> >> And, I think finally, if you need different php.ini files for apache >> and the command line, ... >> >> "If php-SAPI.ini exists (where SAPI is used SAPI, so the filename is >> e.g. php-cli.ini or php-apache.ini), it's used instead of php.ini. >> SAPI name can be determined by php_sapi_name()." >> >> Richard. >> >> -- >> Richard Quadling >> Twitter : EE : Zend : PHPDoc >> @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vF
Re: [PHP-DB] how does this regular expression works
On 13 July 2011 14:04, Shahriyar Imanov wrote: > \(?P.*?)\<\/head\> (?P.*?) Options: case insensitive; ^ and $ match at line breaks Match the characters “” literally «>» Match the regular expression below and capture its match into backreference with name “head_tag_innerHTML” «(?P.*?)» Match any single character that is not a line break character «.*?» Between zero and unlimited times, as few times as possible, expanding as needed (lazy) «*?» Match the characters “” literally «» Created with RegexBuddy Escaping < > ? at the wrong time is simply a redundancy. Learning when to escape is just like learning a new language. You only need to escape anything if you need it to be a literal character. e.g. something.html vs something\.html -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how does this regular expression works
On 13 July 2011 13:42, who.cat wrote: > ]*>(.*?) ]*>(.*?) Options: case insensitive; ^ and $ match at line breaks Match the characters “” «[^>]*» Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*» Match the character “>” literally «>» Match the regular expression below and capture its match into backreference number 1 «(.*?)» Match any single character that is not a line break character «.*?» Between zero and unlimited times, as few times as possible, expanding as needed (lazy) «*?» Match the characters “” literally «» Created with RegexBuddy -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Listing parent ids
On 28 July 2011 10:39, Arno Kuhl wrote: > Arno Kuhl wrote: >> Not strictly a php issue but it's for a php app, hope that counts >> (plus I haven't had much joy googling this) >> >> I have a table with an id and a parentid. >> If it's a top-level record the parentid is 0, otherwise it points to >> another record, and if that record isn't a top-level record its >> parentid points to another record, etc (a linked list). >> >> Is there a single select that will return the complete list of parentids? >> Or do I have to iterate selecting each parent record while parentid> >> 0 and build the list entry by entry? > > Little difficult to answer what you don't say what you are using as a > database. > Recursive queries are now possible on many databases, except I think for > MySQL. > I run this type of query all the time on Firebird and Postgres now supports > the same CTE functions. > > -- > Lester Caine - G8HFL > - > > > I'm currently using MySQL but I'll switch databases if there's a compelling > reason and no drawbacks. > Thanks for the lead, I'm googling recursive queries. > > Cheers > Arno > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > I would take a long hard read of this article http://web.archive.org/web/20100105135622/http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I can't find it anywhere else now - it used to be on the mysql site - but gone since Oracle has it and I can't find it in Google Cache. But, it explains the pros and cons of using the Adjacency List Model vs the Nested Set Model. The article is quite old (the copyright on the page is 2008, but I've no idea when it was actually created) and so, there are advances in SQL features (CTE's being one of them) which aren't mentioned. But, I've found Nested Sets to be much easier for me to work with, allowing me to provide quite complex searching based upon an n-level tree. How you visualise the data won't change. It is still, visually at least, a set of parent/child relationships, but to build a tree, you don't need to use recursion. In most cases, a single query will be enough to interact with the tree at any level, in any direction, for more or less any purpose. I recently bought "Joe Celkos SQL for Smarties: Advanced SQL Programming" on eBay. It covers a LOT more about Nested Sets. (http://desc.shop.ebay.co.uk/i.html?_nkw=Celkos+SQL+Smarties&_sacat=0&_dmpt=Non_Fiction&_odkw=Celkos+SQL+Smarties&_osacat=0&_trksid=p3286.c0.m270.l1313&LH_TitleDesc=1 currently showing 2 entries) and a fourth edition on Amazon (http://www.amazon.com/Joe-Celkos-SQL-Smarties-Fourth/dp/0123820227/ref=sr_1_1?ie=UTF8&qid=1311847652&sr=8-1) -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Listing parent ids
And http://www.amazon.co.uk/exec/obidos/ASIN/1558609202/onlinepricecouk -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Listing parent ids
On 28 July 2011 12:30, Arno Kuhl wrote: > On 28 July 2011 10:39, Arno Kuhl wrote: >> Arno Kuhl wrote: >>> Not strictly a php issue but it's for a php app, hope that counts >>> (plus I haven't had much joy googling this) >>> >>> I have a table with an id and a parentid. >>> If it's a top-level record the parentid is 0, otherwise it points to >>> another record, and if that record isn't a top-level record its >>> parentid points to another record, etc (a linked list). >>> >>> Is there a single select that will return the complete list of parentids? >>> Or do I have to iterate selecting each parent record while parentid> >>> 0 and build the list entry by entry? >> >> Little difficult to answer what you don't say what you are using as a >> database. >> Recursive queries are now possible on many databases, except I think >> for MySQL. >> I run this type of query all the time on Firebird and Postgres now >> supports the same CTE functions. >> >> Lester Caine - G8HFL >> - >> >> I'm currently using MySQL but I'll switch databases if there's a >> compelling reason and no drawbacks. >> Thanks for the lead, I'm googling recursive queries. >> >> Arno >> -- > > I would take a long hard read of this article > http://web.archive.org/web/20100105135622/http://dev.mysql.com/tech-resource > s/articles/hierarchical-data.html > > I can't find it anywhere else now - it used to be on the mysql site - but > gone since Oracle has it and I can't find it in Google Cache. > > But, it explains the pros and cons of using the Adjacency List Model vs the > Nested Set Model. > > The article is quite old (the copyright on the page is 2008, but I've no > idea when it was actually created) and so, there are advances in SQL > features (CTE's being one of them) which aren't mentioned. > > But, I've found Nested Sets to be much easier for me to work with, allowing > me to provide quite complex searching based upon an n-level tree. > > How you visualise the data won't change. It is still, visually at least, a > set of parent/child relationships, but to build a tree, you don't need to > use recursion. In most cases, a single query will be enough to interact with > the tree at any level, in any direction, for more or less any purpose. > > Richard Quadling > -- > > Thanks Richard. Your reference is exactly what I was looking for. > I'm just busy reading a sitepoint article about adjacent lists vs the > niftily titled "modified preorder tree traversal model". > http://www.sitepoint.com/hierarchical-data-database/ (really old - 2003) > I found I'm using the adjacent list model at the moment (didn't know it had > a name, I always thought of it as a type of linked list). > The "modified preorder tree traversal model" in the sitepoint article > appears to be equivalent to the "nested set model" in the mysql article. > It seems simple enough to implement, I'll definitely give it a closer look > and do some tests. > > Thanks, it's great to hear the experiences of others who've used this. > > Cheers > Arno > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > In my day job, I process a LOT of data (MS SQL Server, 15 or so DBs, maybe 250GB of data, at least 15 years of trends,etc.). So I have a LOT of trees. Customer hierarchies (Customer Head Office, Regional Office, Branch), Location hierarchies (Continent, Country, Region, City, Street), Product hierarchies. BOM, etc. When it comes to analysis, I can ask questions like "Which European Customers have, overall, increased their turnover by at least 20% in the last 6 months for a single product type?" Because of the nested sets, I know that "European" means a left/right of 23 to 224. Any customer branch with a location id in that range is eligible for inclusion. No recursion of finding the European ID and then chugging through all the IDs down to the street level to match that ID to the customer. And then realising that not all the customers are tagged at street level, but maybe just at the city level. It allows faster grouping and drilldown in my mind as the data is always filtered for the required set in question. And if you are filtering over multiple sets (location, date, product category), you are going to get to the results a LOT faster than with the easier understood, but not as useful (IMHO) adjacent list. Richard. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Listing parent ids
On 28 July 2011 14:09, Arno Kuhl wrote: >> >> I have a table with an id and a parentid. >> If it's a top-level record the parentid is 0, otherwise it points to >> another record, and if that record isn't a top-level record its >> parentid points to another record, etc (a linked list). >> >> Is there a single select that will return the complete list of parentids? > > You say you have a parentid and an id - ie, two specific fields in your > records. You say that you want to query all the parentids. Nobody else has > said this, but why not just > > Select unique ids where parentid=0 ? That gives you (as you say) all your > top-level records, which are the parents of everything, no? Or are you > looking for each id that is itself a parent to something else? If the > latter, then why not > select unique parentid where parentid <> 0 ? > > -- > > Hi Jim. I wanted the list of related parentids from current id to top-level > (parentid=0). > That could be 0 or more results, regardless of how many non-zero parentids > there are in total. > Currently I get this from selecting id=parentid while parentid>0, ie > recursively select each record going up the tree to the top level. > The nested set model apparently can return the entire hierarchical list from > the current node to the top level with a single select, but I haven't run > any tests yet on my specific data. > > Cheers > Arno > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If your data is based upon parentid and uniqueid, then you will need to change your data... http://data.bangtech.com/sql/nested_set_treeview.htm and once you've done that and find you can't deal with things (maybe it isn't for you), then http://pratchev.blogspot.com/2007/02/convert-tree-structure-from-nested-set.html will help you turn it all back again. I've not used either of those but on the surface they seem adequate. But just look at the difference in the volume of code. NS => AL ... 1 query. Any depth. It shows just how simple using a NS is. I'll stop evangelising now. I REALLY like NS. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: MYSQLi fetch corrupting bound variables
On 25 August 2011 14:07, Jim Giner wrote: > First glance: your 'if' needs '= =' not '=' No. Fully ... if (False !== ($stmt = $dbObj->prepare(...))) { ... } In PHP, assignments evaluate to the value assigned. If the value assigned can be evaluated as TRUE then the above test will pass. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.
Hi. I'm just trying to get PHP to talk to a stored procedure which has IN, INOUT and OUT parameters. The procedure is really dumb as I'm just trying to get things to work. The procedure is (taken from NaviCat's DDL view of the procedure) ... CREATE DEFINER=`root`@`localhost` PROCEDURE `testInOuts`(IN `anInputInt` int,INOUT `anInputOutputInt` int,OUT `anOutputDateTime` datetime) BEGIN SET anInputOutputInt = anInputInt + anInputOutputInt; SET anOutputDateTime = NOW(); END I've proven that the procedure work by ... CREATE DEFINER=`root`@`localhost` PROCEDURE `tester`() BEGIN DECLARE myNewInt INT; DECLARE myNewDT DateTime; SET myNewInt = 5; CALL testInOuts(10, myNewInt, myNewDT); SELECT myNewInt, myNewDT; END And the output is 15 and today's datetime. But I'm getting nowhere fast with PHP and mysqli. I've got stored procedures returning result sets - all happy with that. But not via OUT or INOUT. connect_errno) { die('Error #' . $o_Conn->connect_errno . ' : ' . $o_Conn->connect_error); } $o_Stmt = $o_Conn->prepare('CALL test.testInOuts(?,?,?)') or die('Error #' . $o_Conn->errno . ' : ' . $o_Conn->error); $ten = 10; $five = 5; $when = Null; $o_Stmt->bind_param('iid', $ten, $five, $when) or die('Error #' . $o_Conn->errno . ' : ' . $o_Conn->error); $o_Stmt->execute() or die('Error #' . $o_Conn->errno . ' : ' . $o_Conn->error); echo '$ten = ', $ten, PHP_EOL, '$five = ', $five, PHP_EOL, '$when = ', $when, PHP_EOL; ?> outputs ... $ten = 10 $five = 5 $when = No errors and no changes to the params. They are supplied by reference to the bind_params() method, so I would assume that they would be returned by calling execute(). But they aren't so I'm missing something obvious. Any help please? Regards, Richard. P.S. I'm coming from MSSQL using the MS SQL Server Driver for PHP via PDO, so a very different experience. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.
On 13 September 2011 04:21, Ross McKay wrote: > On Mon, 12 Sep 2011 21:18:34 +0100, Richard Quadling wrote: > >>I'm just trying to get PHP to talk to a stored procedure which has IN, >>INOUT and OUT parameters. >>[...] > > You'll probably need to trick it into returning a row with your output > params, like using this multi-statement query: > > "CALL testInOuts(10, @myNewInt, @myNewDT);SELECT @myNewInt, @myNewDT" > > see TFM for multi-query usage: > > http://au2.php.net/manual/en/mysqli.multi-query.php > >>P.S. I'm coming from MSSQL using the MS SQL Server Driver for PHP via >>PDO, so a very different experience. > > Indeed, PDO is probably what you should be using if you want to use > output parameters and MySQL in PHP. I've seen this "return a row" mechanism. But that allows me to define IN parameters and to capture OUT params, but does nothing for INOUTs (as far as I can tell). I'm in the process of de-coupling the code from SQL in PHP to use prepared statements with stored procedures. A large number of the SPs return 2 or 3 values and so OUT params was ideal. I want to use PDO. There is no php_myslqi extension, just php_mysql. I don't think I can use multi_query AND prepared statements. I don't want to be going back a decade in development and having to manually prepare SQL statements and escape/test parameters when there is a nearly perfect mechanism already available. I think moving from OUT and INOUT to a normal result set would be the fastest way to move forward. Thanks. Richard. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Exporting mySQL to Excel
On 8 November 2011 18:31, Ron Piggott wrote: > What is the preferred method used to export mySQL to Excel within the context > of PHP? I have looked on Google and found a wide variety of options. Ron > > Ron Piggott If the mysql server is set to allow remote access, then you could use ODBC and Excel to grab the data directly. Alternatively, if you have SSH or some sort of tunnelling capability, then with the tunnel in place, it would look like a local connection and again, ODBC with Excel could be your answer. I use NaviCat Lite to connect to mysql servers running inside Amazon's cloud services. I also Putty to create the tunnel and map the connection to allow the GoodData client to gather data from the mysql server for further reporting at GoodData. -- Richard Quadling Twitter : EE : Zend : PHPDoc : Fantasy Shopper @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea : fan.sh/6/370 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Exporting mySQL to Excel
On 9 November 2011 23:52, Matijn Woudt wrote: > On Thu, Nov 10, 2011 at 12:32 AM, Ron Piggott > wrote: >> >> I am wondering how phpmyadmin makes Excel files "on the fly" --- Is it a >> class? >> > > They used PHPExcel in the past, but they had to remove because of > license issues. Don't know what they're currently using, but if you > can live with LGPL, PHPExcel might be an option. > > Matijn > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If you can specify a TAB delimited file (assuming you don't have tab characters in your data), then this is a slight upgrade to CSV. -- Richard Quadling Twitter : EE : Zend : PHPDoc : Fantasy Shopper @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea : fan.sh/6/370 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQLi binding params and results and where in a loop they should be placed.
Hi. Just looking for some clarity before I spend an hour trying to figure out if I'm doing it right. I'm reading a stream of product data from an external source. I'm needing to tag the data as it goes through the system, indicating if the product has been seen before. So, I'm using a prepared statement with 2 params that identify the product uniquely from the external source. So, I have a prepare(), bind_param(), execute(), bind_result() and fetch(). I think prepare() and bind_param() take place outside of the loop. And population of the bound variables, execute(), bind_result() and fetch() all take place inside the loop. Is that right? Or can I bind_result BEFORE execute(), so all I need to do is populate the bound variable (which is done from the reading from the external stream), execute() and fetch(). The examples in the manual don't really demonstrate the re-use of a prepared statement, making it both a little vague and pointless (excluding the OBVIOUS advantage of sql injection protection - I'm using prepared statements mainly for that reason). -- Richard Quadling Twitter : EE : Zend : Fantasy Shopper @RQuadling : http://e-e.com/M_248814.html : http://bit.ly/9O8vFY : http://fan.sh/106/5tw
Re: [PHP-DB] pdo ?
On 24 January 2013 17:48, Matt Pelmear wrote: > On 01/24/2013 09:23 AM, Jim Giner wrote: >> >> On 1/24/2013 12:05 PM, Matt Pelmear wrote: >>> >>> >>> http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post >>> >>> >>> Every pro has this feature (magic_quotes_gpc) turned off. If you >>> understand SQL Injection vulnerabilities, and properly bind things into >>> your queries, I would recommend disabling it. >>> >>> -Matt >>> >>> On 01/24/2013 08:55 AM, Jim Giner wrote: ok - new to using pdo functions, but I thought I had a handle on it. I'm writing out to my page an input tag with the following value in it: 49'ers I can confirm it by using my browser's "view source" to see that is exactly how it exists in the page. When I hit a submit button and my script retrieves the 'post' vars my debugging steps are showing that the var $_POST['team'] contains the above value with a backslash (\) already inserted. This is causing me a problem when I then try to use pdo->quote to safely encode it for updating my sql database. My question is - why does the POST var show the \ char before I execute the 'quote' function? >>> >> You're right! But I must not understand something. >> >> My root folder has a php.ini file with the magic quotes set off. Doesn't >> that carry on down to folders beneath it? >> > > I would check phpinfo() to see if it is being overridden. > > -Matt Create an info.php file containing ... http://e-e.com/M_248814.html Zend : http://bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Sorting a PHP array
I have an inline result set sorting function (array_multisort_by_columns() sort of thing) as array_multisort wants the data in columns for sorting. I can supply it if anyone wants it. On 16 May 2013 12:57, Jim Giner wrote: > On 5/15/2013 1:38 PM, Bastien wrote: > >> >> >> Bastien Koert >> >> On 2013-05-15, at 12:32 PM, "Charlie Lewis" >> wrote: >> >> I have a bookseller database list read into a PHP array with dimensions >>> [row][field]. There are 32 fields in each record/row and up to 500 >>> records. >>> >>> >>> >>> What is the neatest way to sort the array by any of the fields, such as >>> author or title? >>> >>> >>> >>> I know I should just search for a manual on this, but could anybody give >>> me >>> a quick start? >>> >>> >>> >>> Thanks, >>> >>> >>> >>> Charlie >>> >>> >> Mildly annoyed by your laziness so ima gonna point you to the manual >> >> http://ca1.php.net/manual/en/**function.array-multisort.php<http://ca1.php.net/manual/en/function.array-multisort.php> >> >> Bastien >> >>> >>> >> I learned something. Never read up on this function. Could be useful > someday > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Richard Quadling Twitter : @RQuadling EE : http://e-e.com/M_248814.html Zend : http://bit.ly/9O8vFY
[PHP-DB] Wanted : Solid/documented/tests Nested Set Model class for mySQL.
Hi. I'm looking for a class that implements nested sets, ideally using ZF (1 or 2) or PEAR, or, if standalone, clear enough documentation. My data is essentially social referral and will be nested very deep, so this would seem to be a perfect match for this sort of work, but I'm being a little lazy and looking for a good recommendation. I've done searches on google, et al, but looking for recommendations from real use, rather than "this looks good". Ta. -- Richard Quadling Twitter : @RQuadling EE : http://e-e.com/M_248814.html Zend : http://bit.ly/9O8vFY
Re: [PHP-DB] Re: Problem with query
On 23 June 2013 21:37, Ethan Rosenberg, PhD wrote: > On 6/23/2013 2:31 PM, Ethan Rosenberg, PhD wrote: > >> Dear List - >> >> There is an error in my query, and I cannot find it. >> >> This fails: >> >> $_SESSION['Cust_Num'] = $_REQUEST['cnum']; >> $_SESSION['CustNum'] = $_REQUEST['cnum']; >> >> echo "session"; //this has the proper values >> print_r($_SESSION); >> >> $sql10 = "select Balance, Payments, Charges, Date from Charges where >> Cust_Num = $_SESSION[Cust_Num] order by Date"; >> echo $sql10; //echos the correct query >> $result10 = mysqli_query($cxn, $sql10); >> var_dump($result1); // this returns NULL >> > > Against my better judgement, here I go again. > > Is this the "actual" code you executed, or is it once again a typeover? > > Your 1st error is in these two lines: > >> $result10 = mysqli_query($cxn, $sql10); >> >> var_dump($result1); // this returns NULL >> > > Yes your dump returns null. And always will. > > > Any further errors might be related to your non-standard syntax for the > session variable. Per the manual, associative arrays using string indices > should always use ' ' around them. They work (as mentioned in the manual) > but are wrong. > === > Jim - > > > > Is this the "actual" code you executed, or is it once again a typeover? > > The actual code > > > Any further errors might be related to your non-standard syntax for the > session variable. Per the manual, associative arrays using string indices > should always use ' ' around them. They work (as mentioned in the manual) > but are wrong. > > Newbie is confused. > > Please explain. Try ... $sql10 = "select Balance, Payments, Charges, Date from Charges where Cust_Num = {$_SESSION['Cust_Num']} order by Date"; -- Richard Quadling Twitter : @RQuadling EE : http://e-e.com/M_248814.html Zend : http://bit.ly/9O8vFY
Re: [PHP-DB] Problem wkith Query
Turn on error reporting/logging/displaying and what errors are you getting? And as you said ... $result10 = mysqli_query($cxn, $sql10); var_dump($result1); // this returns NULL is your actual code, maybe ... gives you a better clue? On 23 June 2013 23:06, Ethan Rosenberg, PhD wrote: > > > > > On 23 June 2013 21:37, Ethan Rosenberg, PhD *com > wrote: > > On 6/23/2013 2:31 PM, Ethan Rosenberg, PhD wrote: > > Dear List - > > There is an error in my query, and I cannot find it. > > This fails: > > $_SESSION['Cust_Num'] = $_REQUEST['cnum']; > $_SESSION['CustNum'] = $_REQUEST['cnum']; > > echo "session"; //this has the proper values > print_r($_SESSION); > > $sql10 = "select Balance, Payments, Charges, Date from Charges > where > Cust_Num = $_SESSION[Cust_Num] order by Date"; > echo $sql10; //echos the correct query > $result10 = mysqli_query($cxn, $sql10); > var_dump($result1); // this returns NULL > > > Against my better judgement, here I go again. > > Is this the "actual" code you executed, or is it once again a typeover? > > Your 1st error is in these two lines: > > $result10 = mysqli_query($cxn, $sql10); > > var_dump($result1); // this returns NULL > > > Yes your dump returns null. And always will. > > > Any further errors might be related to your non-standard syntax for > the session variable. Per the manual, associative arrays using string > indices should always use ' ' around them. They work (as mentioned in the > manual) but are wrong. > === > Jim - > > > > Is this the "actual" code you executed, or is it once again a typeover? > > The actual code > > > Any further errors might be related to your non-standard syntax for > the session variable. Per the manual, associative arrays using string > indices should always use ' ' around them. They work (as mentioned in the > manual) but are wrong. > > Newbie is confused. > > Please explain. > > > Try ... > > $sql10 = "select Balance, Payments, Charges, Date from Charges where > Cust_Num = {$_SESSION['Cust_Num']} order by Date"; > > > -- > Richard Quadling > Twitter : @RQuadling > EE : http://e-e.com/M_248814.html > Zend : http://bit.ly/9O8vFY > = > Tried it. No luck > > Ethan > -- Richard Quadling Twitter : @RQuadling EE : http://e-e.com/M_248814.html Zend : http://bit.ly/9O8vFY
Re: [PHP-DB] MS Access Connection with database password
Did you read the notes regarding password length, password content and Access version issues? Do any of these apply to you? I suspect one of them does. On 4 June 2014 21:48, Kjell Hansen wrote: > Hi, > I'm trying to connect to a MS Access database that has a database password > set. > According to http://www.connectionstrings.com/access/ you use the > folowing as a connection string in such cases: > Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; > Jet OLEDB:Database Password=MyDbPassword; > > Which I translate into > $dbcon = new PDO( 'odbc:Driver={Microsoft Access Driver > (*.mdb)};DBQ=C:\\exampledb.mdb;Jet OLEDB:Database Password=MyDbPassword;' > ); > But it doesn't work :( > When I have removed the password, there's no problem connecting but not > with password set. > > I don't make the database, it's maintained elsewhere and I plan to > download it regularly and extract data from it so I need to connect to the > database with the password set. > > Any help or hints are deeply appreciated! > /Kjell > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Richard Quadling Twitter : @RQuadling EE : http://e-e.com/M_248814.html Zend : http://bit.ly/9O8vFY
Re: [PHP-DB] Code Security
On 5 February 2015 at 05:52, Ethan Rosenberg < erosenb...@hygeiabiomedical.com> wrote: > > How do I prevent someone from opening a terminal window, going to /var/www > and stealing all my code? > 1 - Don't allow terminal access to your box. 2 - Use a PHP byte code encoder (IonCube, Zend Guard) - not perfect as they can be reversed to access the code in a form. 3 - Don't use PHP. -- Richard Quadling
Re: [PHP-DB] Lost data after space in PHP variables
On 17 February 2015 at 20:36, Charlie Lewis wrote: > I'm not sure that this is entirely the group for my question, so apologise > if it is. > > > > I'm losing data in variables that use the assignment of $_POST[]. Any > variable with a space in the $_POST[] loses everything after the space. Is > it that I need to enclose the data with ' or "? I've tried all that with > little success. > > Using a simple script like this ... Testing spaces in names results in ... array ( 'a_b' => 'c d', )array ( 'e_f' => 'g h', ) So, your spaces are automatically converted to _ by PHP. This is documented at http://php.net/manual/en/language.variables.external.php -- Richard Quadling
[PHP-DB] Re: [PHP-WIN] Need Help with 2 MYSQL Table
On 25 April 2015 at 06:13, Guru wrote: > Hi All, > > Hope everyone is doing well. I need some help regarding a project. I have a > huge list of customers in a ledger in a mysql table (Table A). And in > another table I have their names and their ID number (Table B). What I want > is when I click on a customer name from Table A, I want php to pull out > the data of that particular customer from Table B, and show it in a > formatted tabular manner. Please help me with this. > > Where to start. Well. What have you got at the moment? You will find it easier to get help if you offer what you've got at the moment. Are you using any framework or raw PHP? Regards, Richard.