Re: [PHP] how to extract fields from associative array into different variables
On Mon, 2009-06-15 at 17:38 -0400, PJ wrote:
> Jay Blanchard wrote:
> > [snip]
> > In what way would this simplify or ease my pain?
> > The difficulty, it seems to me, is not in retrieving the rows, but
> > rather how to pass the row data to the variables. And since the number
> > of rows is variable, I believe that the only way to assign the variables
> > is by use of a loop? I think I'm beating my head against the wall for
> > nothing... :-(
> > [/snip]
> >
> > You asked for easier. In this way the data is assigned to a usable
> > variable right out of the gate and that is an array variable. You can
> > actually use it with either mysql_fetch_array or mysql_fetch_row.
> >
> > Here is your query;
> >
> > $sql = "SELECT first_name, last_name, book_author.ordinal
> > FROM author, book_author
> > WHERE book_author.bookID = $idIN && book_author.authID = author.id
> > ORDER BY ordinal";
> >
> > You need not declare another array, but do it this way instead;
> >
> >if ($results = mysql_query($sql, $db)) {
> > while ( $row = mysql_fetch_array($results, MYSQL_ASSOC) ) {
> > echo $row['first_name'];
> > echo $row['last_name'];
> >
> Then I have to add some loopy thing to assign the values to the
> $variables... a real pita since my variable do not lend themselves too
> well to linear alterations ($varIn, $var2IN, $var3IN... etc... so $i=0
> and $i++ arren't going to be too cooperative here... I guess I'd have to
> change that to $var1IN... and then figure out how to do $var.$i.IN ... I
> already tried, but don't seem to have it right
> > etcetera.
> > }
> > }
> >
> > The lazy part I will agree with :)
> >
> Well, I shouldn't say it, but laziness is a characteristic of
> "intelligence": why do more than you have to when you can be doing
> something else. Actually, I am anything but lazy, I spend innumerable
> hours trying to understand what all this coding is about... searching
> the web and rummaging (and I do mean "rummaging") in all the lists and
> posts ... but just going through 20 or 30 listings out of more than
> 20,000 is already taxing... much easier to ask on the list... if someone
> can understand my fuzzy questions, they may find the grace and
> generosity to take pity on the ignoramus... ;-)
> And I enjoy the ribbing and the humour ... it's a really very nice list!
>
>
> --
> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
> -
> Phil Jourdan --- p...@ptahhotep.com
>http://www.ptahhotep.com
>http://www.chiccantine.com/andypantry.php
>
>
Why do you need them in separate variables? Can't you use them using
array syntax elsewhere in your code?
Thanks
Ash
www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] I2C and PHP
Hi,
I'm working on a interface between the I2C Bus (Hardware) and PHP - to use
the PHP as webinterface to control an I2C Device.
The bus is mounted as: /dev/i2c8_aux - and works
opening the bus with
$fd=fopen (/dev/i2c8_aux, w (r+) (r+b) (wb))is successfull
setting the address of the slave device (0xB0 (=0x58) and the register
(between 0-15) works
fseek($fd, 0x5801) (accessing register 01)
Now - the things that doesn't work: writting into the register
I'd like to write a value between 0-255 (0x00 and 0xff) e.g into register 1
(the one from above) - (0 = turn left, 128=stop, 255=turn right) I can only
write one (1) byte into the register, and fwrite uses strings.
$data = 128
$rslt = ($fd, $data, 1)
would write 3bytes.
So, what I need is a way to convert any value between 0 and 255 into a
string with one byte, e.g
0xff = (with strlen($data) == 1)
I tried it with:
chr(255) - not successfull
pack("H" and "h", 0xff) - not successfull
decbin(255) - without success
And many more variations
Any ideas, suggestions, Thanks!
tobias
[PHP] I2C, sending a binary byte with fwrite
Hi,
I'm working on a interface between the I2C Bus (Hardware) and PHP - to use
the PHP as webinterface to control an I2C Device.
The bus is mounted as: /dev/i2c8_aux - and works
opening the bus with
$fd=fopen (/dev/i2c8_aux, w (r+) (r+b) (wb))is successfull
setting the address of the slave device (0xB0 (=0x58) and the register
(between 0-15) works
fseek($fd, 0x5801) (accessing register 01)
Now - the things that doesn't work: writting into the register
I'd like to write a value between 0-255 (0x00 and 0xff) e.g into register 1
(the one from above) - (0 = turn left, 128=stop, 255=turn right) I can only
write one (1) byte into the register, and fwrite uses strings.
$data = 128
$rslt = ($fd, $data, 1)
would write 3bytes.
So, what I need is a way to convert any value between 0 and 255 into a
string with one byte, e.g
0xff = (with strlen($data) == 1)
I tried it with:
chr(255) - not successfull
pack("H" and "h", 0xff) - not successfull
decbin(255) - without success
and many more.
Any advice, suggestion, experience? Thanks!
Tobias
Re: [PHP] fopen() on a network share?
On Mon, Jun 15, 2009 at 7:24 PM, Shawn McKenzie wrote:
> Brian Dunning wrote:
>> Extra info, in case needed: my code says
>>
>> fopen('\\servername\sharename\folder\file.xml', 'w');
>>
>> and it returns "Failed to open stream, no such file or directory". I've
>> verified that the PHP machine does have unrestricted permissions to that
>> share and to the directory. Thanks.
>>
>>
>> On Jun 15, 2009, at 1:39 PM, Brian Dunning wrote:
>>
>>> Running on Windows... I have a network share, \\sharename\foldername,
>>> and I want to write a file. How do I format the pathname with fopen()
>>> for this?
>>
>>
>>
> As I remember, you either have to double slash or use the other slash.
>
> servername\\sharename\\folder\\file.xml
>
> or
>
> //servername/sharename/folder/file.xml
>
> --
> Thanks!
> -Shawn
> http://www.spidean.com
>
I think 'servername\sharename\folder\file.xml' will work if you're
using single quotes around the string. The only slashes that would
need escaped are the first two since the first slash in '\\' escapes
the second.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] fopen() on a network share?
Brian Dunning wrote:
> Extra info, in case needed: my code says
>
> fopen('\\servername\sharename\folder\file.xml', 'w');
>
> and it returns "Failed to open stream, no such file or directory". I've
> verified that the PHP machine does have unrestricted permissions to that
> share and to the directory. Thanks.
>
>
> On Jun 15, 2009, at 1:39 PM, Brian Dunning wrote:
>
>> Running on Windows... I have a network share, \\sharename\foldername,
>> and I want to write a file. How do I format the pathname with fopen()
>> for this?
>
>
>
As I remember, you either have to double slash or use the other slash.
servername\\sharename\\folder\\file.xml
or
//servername/sharename/folder/file.xml
--
Thanks!
-Shawn
http://www.spidean.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] fopen() on a network share?
Extra info, in case needed: my code says
fopen('\\servername\sharename\folder\file.xml', 'w');
and it returns "Failed to open stream, no such file or directory".
I've verified that the PHP machine does have unrestricted permissions
to that share and to the directory. Thanks.
On Jun 15, 2009, at 1:39 PM, Brian Dunning wrote:
Running on Windows... I have a network share, \\sharename
\foldername, and I want to write a file. How do I format the
pathname with fopen() for this?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] populate form input option dropdown box from existing data
I am having difficulties figuring out how enter retrieved data into a dropdown box for editing. Here's a snippet: ...snip Civilization Monuments, Temples & Tombs Pharaohs and Queens... snip As I understand it, I need to put an array ( $categoriesIN[] ) somewhere in the above code... or do I need to insert a series of value "selected" fields for the values? The closest thing to what I need, seems to be in this post: http://www.hpfreaks.com/forums/index.php?topic=165215 But it doesn't appear too inviting... not sure if they ever got it to work... -- Hervé Kempf: "Pour sauver la planète, sortez du capitalisme." - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how to extract fields from associative array into different variables
Jay Blanchard wrote:
> [snip]
> In what way would this simplify or ease my pain?
> The difficulty, it seems to me, is not in retrieving the rows, but
> rather how to pass the row data to the variables. And since the number
> of rows is variable, I believe that the only way to assign the variables
> is by use of a loop? I think I'm beating my head against the wall for
> nothing... :-(
> [/snip]
>
> You asked for easier. In this way the data is assigned to a usable
> variable right out of the gate and that is an array variable. You can
> actually use it with either mysql_fetch_array or mysql_fetch_row.
>
> Here is your query;
>
> $sql = "SELECT first_name, last_name, book_author.ordinal
> FROM author, book_author
> WHERE book_author.bookID = $idIN && book_author.authID = author.id
> ORDER BY ordinal";
>
> You need not declare another array, but do it this way instead;
>
>if ($results = mysql_query($sql, $db)) {
> while ( $row = mysql_fetch_array($results, MYSQL_ASSOC) ) {
> echo $row['first_name'];
> echo $row['last_name'];
>
Then I have to add some loopy thing to assign the values to the
$variables... a real pita since my variable do not lend themselves too
well to linear alterations ($varIn, $var2IN, $var3IN... etc... so $i=0
and $i++ arren't going to be too cooperative here... I guess I'd have to
change that to $var1IN... and then figure out how to do $var.$i.IN ... I
already tried, but don't seem to have it right
> etcetera.
> }
> }
>
> The lazy part I will agree with :)
>
Well, I shouldn't say it, but laziness is a characteristic of
"intelligence": why do more than you have to when you can be doing
something else. Actually, I am anything but lazy, I spend innumerable
hours trying to understand what all this coding is about... searching
the web and rummaging (and I do mean "rummaging") in all the lists and
posts ... but just going through 20 or 30 listings out of more than
20,000 is already taxing... much easier to ask on the list... if someone
can understand my fuzzy questions, they may find the grace and
generosity to take pity on the ignoramus... ;-)
And I enjoy the ribbing and the humour ... it's a really very nice list!
--
Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Preventing XSS Attacks
On Mon, Jun 15, 2009 at 10:48:04AM -0400, Bob McConnell wrote: > From: Ashley Sheridan > > On Wed, 2009-06-10 at 18:28 +0200, Nitsan Bin-Nun wrote: > >> mysql_real_escape_string() only sanitise the input. I would > personally > >> only allow [a-zA-Z0-9-_] in search string but that's just me ;) > >> Validate the input in some way, or make extra sanitisation of it > >> before running the search query. > >> > >> Regarding the HTML output, just entities() it and you'll be good :) > >> > >> On Wed, Jun 10, 2009 at 6:32 PM, Ashley Sheridan > >> wrote: > >> > >> On Wed, 2009-06-10 at 18:18 +0200, Nitsan Bin-Nun wrote: > >> > As far for the output, just html entities () it and you > will > >> be good. > >> > > >> > You better check the search query for sql injection, which > >> is more > >> > dangerous. > >> > > >> > HTH > >> > Nitsan > >> > > >> > On Wed, Jun 10, 2009 at 6:19 PM, Ashley Sheridan > >> > wrote: > >> > Hi all, > >> > > >> > I'm looking at adding a new search feature to my > >> site, and one > >> > of the > >> > elements of this is to echo back in the search > >> results page, > >> > the > >> > original string the user searched for. Up until > now, > >> XSS > >> > hasn't (afaik) > >> > been an issue for my site, but I can see from a > mile > >> off this > >> > will be. > >> > What would you guys recommend to avoid this? > >> > > >> > I'd thought initially of using a mixture of > >> > html_special_chars() and a > >> > regex (as yet not sure what I'll be stripping out > >> with this) > >> > to sanitise > >> > the output for display on the results page, but is > >> this > >> > enough? > >> > > >> > >> I always use mysql_real_escape_string() for that sort of > >> thing, not had > >> a problem with it, but is there anything you think I should > be > >> wary of? > >> > > > > Well, I don't understand, what is the problem with > > mysql_real_escape_string() for sanitising input to use for a search? > It > > should escape anything out so that the query can't be used in ways > that > > I don't want no? > > > > I'd thought about using a whitelist-only regex, but that seems a > little > > limiting tbh, and as my site contains code, it's not unreasonable to > > expect some people might want to search for particular code excerpts. > > What if we don't use MySQL? We are using Postgres on our web servers. > None of the MySQL libraries are available. I am currently reviewing a > half-dozen different and incomplete black-list sanitization functions > that don't to a very good job while removing characters that we need to > be able to use. I need to identify a clean strategy to replace or > restructure them. PostgreSQL has a function called pg_escape_string() which probably performs a function similar to MySQL's function. See http://us2.php.net/manual/en/function.pg-escape-string.php But you'll still need other functions (as above in this thread) to do a thorough job. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] how to extract fields from associative array into different variables
[snip]
In what way would this simplify or ease my pain?
The difficulty, it seems to me, is not in retrieving the rows, but
rather how to pass the row data to the variables. And since the number
of rows is variable, I believe that the only way to assign the variables
is by use of a loop? I think I'm beating my head against the wall for
nothing... :-(
[/snip]
You asked for easier. In this way the data is assigned to a usable
variable right out of the gate and that is an array variable. You can
actually use it with either mysql_fetch_array or mysql_fetch_row.
Here is your query;
$sql = "SELECT first_name, last_name, book_author.ordinal
FROM author, book_author
WHERE book_author.bookID = $idIN && book_author.authID = author.id
ORDER BY ordinal";
You need not declare another array, but do it this way instead;
if ($results = mysql_query($sql, $db)) {
while ( $row = mysql_fetch_array($results, MYSQL_ASSOC) ) {
echo $row['first_name'];
echo $row['last_name'];
etcetera.
}
}
The lazy part I will agree with :)
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] fopen() on a network share?
Running on Windows... I have a network share, \\sharename\foldername, and I want to write a file. How do I format the pathname with fopen() for this? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] how to extract fields from associative array into different variables
You could use list() a la list($foo, $bar) = mysql_fetch_row(); On Mon, Jun 15, 2009 at 4:19 PM, PJ wrote: > Jay Blanchard wrote: > > [snip] > > Is there an easier or simpler way to do this? > > [/snip] > > > > http://us2.php.net/manual/en/function.mysql-fetch-row.php > > > In what way would this simplify or ease my pain? > The difficulty, it seems to me, is not in retrieving the rows, but > rather how to pass the row data to the variables. And since the number > of rows is variable, I believe that the only way to assign the variables > is by use of a loop? I think I'm beating my head against the wall for > nothing... :-( > I know I'm a newbie (a very lazy and ignorant one, apparently) but I > don't quite understand. Or is there a gain in execution time or resource > usage? > > > -- > Hervé Kempf: "Pour sauver la planète, sortez du capitalisme." > - > Phil Jourdan --- p...@ptahhotep.com > http://www.ptahhotep.com >http://www.chiccantine.com/andypantry.php > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP] how to extract fields from associative array into different variables
Jay Blanchard wrote: > [snip] > Is there an easier or simpler way to do this? > [/snip] > > http://us2.php.net/manual/en/function.mysql-fetch-row.php > In what way would this simplify or ease my pain? The difficulty, it seems to me, is not in retrieving the rows, but rather how to pass the row data to the variables. And since the number of rows is variable, I believe that the only way to assign the variables is by use of a loop? I think I'm beating my head against the wall for nothing... :-( I know I'm a newbie (a very lazy and ignorant one, apparently) but I don't quite understand. Or is there a gain in execution time or resource usage? -- Hervé Kempf: "Pour sauver la planète, sortez du capitalisme." - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] how to extract fields from associative array into different variables
[snip] Is there an easier or simpler way to do this? [/snip] http://us2.php.net/manual/en/function.mysql-fetch-row.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] how to extract fields from associative array into different variables
Is there an easier or simpler way to do this?
code:
$sql = "SELECT first_name, last_name, book_author.ordinal
FROM author, book_author
WHERE book_author.bookID = $idIN && book_author.authID = author.id
ORDER BY ordinal";
$author = array();
if ( ( $results = mysql_query($sql, $db) ) !== false ) {
while ( $row = mysql_fetch_array($results, MYSQL_ASSOC) ) {
$author[] = $row;
}
}
$numrows = mysql_num_rows($results);
switch ($numrows)
{
case 5:
$first_nameIN = $author[0]['first_name'];
$last_nameIN = $author[0]['last_name'];
$first_name2IN = $author[1]['first_name'];
$last_name2IN = $author[1]['last_name'];
$first_name3IN = $author[2]['first_name'];
$last_name3IN = $author[2]['last_name'];
$first_name4IN = $author[3]['first_name'];
$last_name4IN = $author[3]['last_name'];
$first_name5IN = $author[4]['first_name'];
$last_name5IN = $author[4]['last_name'];
break;
case 4:
$first_nameIN = $author[0]['first_name'];
$last_nameIN = $author[0]['last_name'];
snip
--
Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Preventing XSS Attacks
From: Ashley Sheridan > On Wed, 2009-06-10 at 18:28 +0200, Nitsan Bin-Nun wrote: >> mysql_real_escape_string() only sanitise the input. I would personally >> only allow [a-zA-Z0-9-_] in search string but that's just me ;) >> Validate the input in some way, or make extra sanitisation of it >> before running the search query. >> >> Regarding the HTML output, just entities() it and you'll be good :) >> >> On Wed, Jun 10, 2009 at 6:32 PM, Ashley Sheridan >> wrote: >> >> On Wed, 2009-06-10 at 18:18 +0200, Nitsan Bin-Nun wrote: >> > As far for the output, just html entities () it and you will >> be good. >> > >> > You better check the search query for sql injection, which >> is more >> > dangerous. >> > >> > HTH >> > Nitsan >> > >> > On Wed, Jun 10, 2009 at 6:19 PM, Ashley Sheridan >> > wrote: >> > Hi all, >> > >> > I'm looking at adding a new search feature to my >> site, and one >> > of the >> > elements of this is to echo back in the search >> results page, >> > the >> > original string the user searched for. Up until now, >> XSS >> > hasn't (afaik) >> > been an issue for my site, but I can see from a mile >> off this >> > will be. >> > What would you guys recommend to avoid this? >> > >> > I'd thought initially of using a mixture of >> > html_special_chars() and a >> > regex (as yet not sure what I'll be stripping out >> with this) >> > to sanitise >> > the output for display on the results page, but is >> this >> > enough? >> > >> >> I always use mysql_real_escape_string() for that sort of >> thing, not had >> a problem with it, but is there anything you think I should be >> wary of? >> > > Well, I don't understand, what is the problem with > mysql_real_escape_string() for sanitising input to use for a search? It > should escape anything out so that the query can't be used in ways that > I don't want no? > > I'd thought about using a whitelist-only regex, but that seems a little > limiting tbh, and as my site contains code, it's not unreasonable to > expect some people might want to search for particular code excerpts. What if we don't use MySQL? We are using Postgres on our web servers. None of the MySQL libraries are available. I am currently reviewing a half-dozen different and incomplete black-list sanitization functions that don't to a very good job while removing characters that we need to be able to use. I need to identify a clean strategy to replace or restructure them. Bob McConnell Sorry for posting this so late, I just got back from a week of vacation. bm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Periodic Actions in PHP?
At 9:22 PM +0330 6/14/09, Parham Doustdar wrote: Actually, your message sparked an idea in my [slow] mind. :) I can put the PHP code in a java script, and tell javascript to call it every fifteen seconds. Best option, I think. :) Any ideas? Thanks! Parham: That's exactly what I've done here: http://webbytedd.com/b/timed-php/ The only addition is that I use ajax. The operation is outlined there. If you need additional details, just ask. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Search/Replace in entire database?
At 9:17 PM -0700 6/14/09, Chris Payne wrote: Hi everyone, I am in the middle of creating an editor where you can search and replace on an individual column in a single table then I came across something I need to be able to do but not sure how. Is it posible (And if so please how :-) to search an entire database and all tables within a database and do a find/replace on keywords without having to specify each table/column within that table? The people I am working for have made some big changes and one of them is changing the names of one of their products, but this product name appears EVERYWHERE in many tables and in lots of different column names, and it would save so much time if I could do a single query that would just search EVERYTHING within the database. Thanks for any advice you can give me. Regards Chris Payne Chris: The problem you cite is exactly why one should use relational databases. If the people you are working for had done that, then you would have only to edit one table. The process works like this -- you have a product table that has an unique id with other information about each product, such as its name. However, every table that references that product does so via the product's id and not the product's name. As such, each look-up for the product name requires first to know the product's id and then pull out the name associated with that id. In any event, a little more work at the start saves tons later. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Uniquely identifying an array... as a key to another array
2009/6/15 Eddie Drapkin : > Alright, here's the situation. I'm wrapping memcached, which takes a list > of several servers, into a quasi-singleton. In other words, I want only one > instance per set of servers, not per server. > > Assuming I had three memcached servers at localhost, 192.168.1.1 and > 192.168.1.2, I wouldn't want one instance per server, but given that there > are seven possible permutations of combinations / only one server > configuration, MemcacheConnector::$instances could be an array with seven > objects. I'm expecting MemcacheConnector::getInstance($array) to take an > array formatted like 'server' => 'port'. > > Thusly, the question then becomes, what's the lightest possible way to store > a unique-ish array as the key for MemcacheConnector::$instances? Having > MemcacheConnector::getInstance() check for > self::$instances[serialize($serverArray)] seems a huge waste of both ram and > CPU time. Is there a better way? Why not give each set of servers a name? Surely they're grouped together for a reason, so give that reason a name. -Stuart -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Search/Replace in entire database?
You can always cheat. Use information_schema or just show databases and show tables and loop through it. Just using information_schema is perfect though i think then you can know or query on column type and save some work. Sent from my iPhone On Jun 15, 2009, at 1:10 AM, Peter Ford wrote: Chris Payne wrote: Hi everyone, I am in the middle of creating an editor where you can search and replace on an individual column in a single table then I came across something I need to be able to do but not sure how. Is it posible (And if so please how :-) to search an entire database and all tables within a database and do a find/replace on keywords without having to specify each table/column within that table? The people I am working for have made some big changes and one of them is changing the names of one of their products, but this product name appears EVERYWHERE in many tables and in lots of different column names, and it would save so much time if I could do a single query that would just search EVERYTHING within the database. Thanks for any advice you can give me. Regards Chris Payne Chris, This is not really a PHP question, is it? More like a question for the support group that corresponds to your database software... However, in my experience databases don't allow a cross-table update in a single query - you won't be able to do it in one query. You will either have to 1. work out which columns and tables contain the name 2. script a query to make the changes for each separately 3. test it on a backup version of the database 4. fix the bugs 5 run the script on the live database. OR (possibly) 1. block access to the database (to prevent any changes while you are processing) 2. dump the whole DB to an SQL script 3. do a search and replace on the text of the SQL script 4. Drop the existing data and reload the database from your SQL dump 5. enable access again so that the users can find the (inevitable) mistakes. These are both pretty time-consuming - sorry! Then make a business case for the project of normalising the database, at least with respect to the product names... -- Peter Ford phone: 01580 89 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Search/Replace in entire database?
Hi Chris, MySQL introduced full - text indexing and searching capabilities back in version 3.23.23. The implementation is straightforward and easy to use — define a FULLTEXT index and use MATCH / AGAINST in the query. Consider this example: CREATE TABLE SOCIAL_EVENT ( EVENT_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, USER_ID INTEGER UNSIGNED NOT NULL, HEADLINE TEXT NOT NULL, EVENT_TEXT TEXT NOT NULL, EVENT_DATE TIMESTAMP NOT NULL, PRIMARY KEY (EVENT_ID), FOREIGN KEY (USER_ID) REFERENCES SOCIAL_USER(USER_ID), FULLTEXT INDEX (HEADLINE, EVENT_TEXT) ) ENGINE=MyISAM DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs AUTO_INCREMENT=0; Thanks. Caner 2009/6/15 Chris Payne > Hi everyone, > > I am in the middle of creating an editor where you can search and > replace on an individual column in a single table then I came across > something I need to be able to do but not sure how. > > Is it posible (And if so please how :-) to search an entire database > and all tables within a database and do a find/replace on keywords > without having to specify each table/column within that table? > > The people I am working for have made some big changes and one of them > is changing the names of one of their products, but this product name > appears EVERYWHERE in many tables and in lots of different column > names, and it would save so much time if I could do a single query > that would just search EVERYTHING within the database. > > Thanks for any advice you can give me. > > Regards > > Chris Payne > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
[PHP] Re: Search/Replace in entire database?
Chris Payne wrote: > Hi everyone, > > I am in the middle of creating an editor where you can search and > replace on an individual column in a single table then I came across > something I need to be able to do but not sure how. > > Is it posible (And if so please how :-) to search an entire database > and all tables within a database and do a find/replace on keywords > without having to specify each table/column within that table? > > The people I am working for have made some big changes and one of them > is changing the names of one of their products, but this product name > appears EVERYWHERE in many tables and in lots of different column > names, and it would save so much time if I could do a single query > that would just search EVERYTHING within the database. > > Thanks for any advice you can give me. > > Regards > > Chris Payne Chris, This is not really a PHP question, is it? More like a question for the support group that corresponds to your database software... However, in my experience databases don't allow a cross-table update in a single query - you won't be able to do it in one query. You will either have to 1. work out which columns and tables contain the name 2. script a query to make the changes for each separately 3. test it on a backup version of the database 4. fix the bugs 5 run the script on the live database. OR (possibly) 1. block access to the database (to prevent any changes while you are processing) 2. dump the whole DB to an SQL script 3. do a search and replace on the text of the SQL script 4. Drop the existing data and reload the database from your SQL dump 5. enable access again so that the users can find the (inevitable) mistakes. These are both pretty time-consuming - sorry! Then make a business case for the project of normalising the database, at least with respect to the product names... -- Peter Ford phone: 01580 89 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
