Using an array(-ish) in SQL queries
X-posted to MySQL and PHP DB Hi gang Task at hand: deleting or selecting (same difference) several numbers of records using only 1 query. My first version simply looped through all the ticked off IDs and ran a single query for each delete routine. I've still not suceeded in getting the delete queries to work on multiple tables at once, despite the column names being the same. But besides this: My current version generates, for multi-select cases, queries like this: DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR `ID`='5' OR `ID`='6' or similar with the SELECT statement. On some occasions this can result in a very large amount of OR statements, like for 50 IDs totally. I've been reading through the MySQL manual and the comments in the select and delete parts, but cannot seem to find any mentioning of an easier way to do this. Or it's been deluting me cuz English is my second language, so the MySQL manual doesn't always make much sense to me. I'm looking for something like passing on an array (as comma-seperated-list maybe), and then just do statements like: DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6) Did I totally miss that part of the manual, or is it just not possible with MySQL ? Now, for my script it doesn't really matter much which approach to use, but was more thinking performance wise it ought to be faster and less taxing for the server to parse an SQL statement that's closer to table structure, rather than the OR statements that has to be transformed first. Sorry if I'm just a blind mouse that can't seem to find things in the MySQL manual. It's not really my best friend... TIA Rene -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ampersands in the database fields
Simple: don't convert it to HTML before inserting it. I used to do that as well, but it causes problems when using searches for several reasons, mostly because you have to convert the search query to HTML, which takes time, and then send the more complex data structure to the server, which also takes more time (albeit extremely little difference). Converting the entries upon retrieval rather than insert is a little bit slower, but it pays off in the searches being more reliable. Rene At 03:14 03-11-2004, leegold wrote: The subject title is a sedgeway into my question that may slightly of topic but I've asked many sources and don't have an answer yet so I ask it here. I have a text fields of html marked up content which I render via php. Looking at the rendered html page in a text editor I see that ampersands all appear as amp; When I select the field via sql I also see amp; I have a pretty standard seach, Fulltext and substring (ie. %keyword%) searches. There's an acronym in my content: NASDand when I try searching for this it really mucks up. The Fulltext no matter what I try does not get a hit. The substring search will work properly if I put the following in the search form:NAamp;SDthat works. But trying the substring search with NASD produces weird results...kinda works but strangely and affects the rendering of the search results page. So what's the cause of all this? Should I upfront load my db text fields differently, or, search them differently - what is the fix via mysql or php for the ampersand problem? Thanks, Lee G. -- Rene Brehmer aka Metalbunny If your life was a dream, would you wake up from a nightmare, dripping of sweat, hoping it was over? Or would you wake up happy and pleased, ready to take on the day with a smile? http://metalbunny.net/ References, tools, and other useful stuff... Check out the new Metalbunny forums at http://forums.metalbunny.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.x: Access denied for users when password is set...
Hi gang as subject says ... I've got a pretty weird situation. For all users I create in the 'user' table in 'mysql' DB, if I set a password, that user becomes unable to access any DBs ... it doesn't matter if I set the pw as ASCII or password, still won't allow access... I've tried making new users and copying all settings of the default root user (which I've not set a PW for cuz I loose access to the main DB when I do that) ... nomatter what, all users can access the DBs fine when no pw is set, but if a pw is set, MySQL reports access denied... and yes, it actually also does this when using the MySQL command line tools which makes it even weirder... This is a single-user test setup for development use only ... I run MySQL 4.0.14b on Windows XP Pro SP1 ... with Apache 2.0.48 and PHP 4.2.3 on CGI. Doesn't matter what kind of DB access I try; the MySQL binaries, PHPMyAdmin, custom made PHP/MySQL apps, or PHPBB ... if I set a PW for a user, that user becomes unable of accessing the DBs ... I've tried opening up the firewall for the MySQL server modules to access the Internet, but does not make a change ... hostname is set to 'localhost' in the mysql table (since all it has to work with are connections from the same windows session anyways)... is there any other place in the mysql table that I have to add/change stuff than 'user' to get this to work??? so lost here ... any help appreciated (and as far as I can tell, I've done every step in all MySQL install guides I could find ... atm I'm running on a restored version of the default mysql DB with the % users set ... since the firewall locks out remote access, I didn't think it's a big deal)... n00b at SQL TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem w. loosing access when using passwords on MySQL 4
hi gang Got a wee bit of a problem with MySQL... Running Distrib 4.0.14, for Win95/Win98 on i32 ... on Windows XP Pro SP1 ... as long as I keep the default setup, with no password on the root user and nothing modder in 'user' at all, I can access the DB. But as soon as I try adding a user or change the password for the root, I loose all contact to the DBs. After changing the root pw, I tried accessing it with \mysql\bin\mysql.exe but it still fails ... tried just restarting the MySQL service, telling MySQLAdmin to reload, and by rebooting the entire computer ... none of it makes any difference tried going through the crappy MySQL manual, but all I got out of it was a headache ... I'm running Apache 2.0.48 and PHP 4.2.3, and latest PHPMyAdmin. As long as there's no pass on the root, I can use PHPMyAdmin just fine ... as soon as I set a password on root, using any method I could find on the web, I loose access (as in it reports 'access denied') This is all just a test/development setup, not an actual server, but nevertheless for security sake I'd like to get the thing working ... tried adding a test install of PHPBB, but it runs into the same problem with the access ... Using PHPMyAdmin I created a user called 'phpbb', and gave it a password, and set everything to 'Y' in the DB... but when I run the install setup for PHPBB I get this: Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in E:\web\phpBB2\db\mysql4.php on line 48 Warning: MySQL Connection Failed: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in E:\web\phpBB2\db\mysql4.php on line 48 Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in E:\web\phpBB2\db\mysql4.php on line 330 Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in E:\web\phpBB2\db\mysql4.php on line 331 phpBB : Critical Error Could not connect to the database ^obviously that's just a PHPBB error, and not an actual MySQL error, but since the problem originates in the MySQL, I figured it's worth mentioning ... PHPBB can't run on the root user without a PW, and since I loose access to the MySQL DBs _when_ I make a PW, I've not done so yet ... so ... does anyone have any ideas for how to get this working like it's supposed to ??? ... I have no clue about MySQL ... never worked with SQL databases till 2 months ago when I did the first MySQL install ... I've changed comps in the meantime, so it's a new installation ... but the problem was the same with the old one ... I figure there's just something I need to change the setting off to get it working, but I have no clue what that would be ... like I said, the MySQL manual is mostly useless to me... TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL shutdown error...
Hi gang Not sure if this is more for the RedHat list than here, but let's try... I've put MySQL on a RedHat by using the RPM ... haven't yet figured out if it actually works... but I had to make the thing start automatically on my own ... so I put mysqld and mysql on the startup list for runlevel 3, which is what I run at... But when it shifts to runlevel 0 or 6, it saysStopping MySQL .. [FAILED]everytime ... now is that essential, or did I do something wrong??? I basically added mysql and mysqld as startup on level 1-5, and on the kill list for level 0 and 6... Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling 4.2.3 with MySQL ... what does it look for?
X-posted to PHP General, PHP DB, and MySQL Hi gang Attempting to get my Linux test-server working, but ran into a problem when making PHP... System is RedHat 8, Apache 1.3.27 (compiled myself, tested OK), MySQL 4.0.13. The Apache 2.0.40 and PHP 4.2.2 that came w. RH8 didn't work correctly, thus I've ventured into my own creation. Trying to build PHP 4.2.3 (because that's what my webhost runs, so need that version to test correctly) w. support for MySQL. Running ./configure --with-mysql=/[path to mysql] --with-apxs=/[path to apxs] Found the path to the apxs to be /usr/local/apache/bin/apxs, but for the life of me I cannot figure out what path to give it for MySQL. I installed MySQL from the the RPM files: MySQL-client-4.0.13-0.i386.rpm MySQL-devel-4.0.13-0.i386.rpm MySQL-embedded-4.0.13-0.i386.rpm MySQL-server-4.0.13-0.i386.rpm MySQL-shared-4.0.13-0.i386.rpm client and server first, the rest second ... used --force to get them in, because it complained about version issues with the one already there (even though the package manager was told not to put the Mysql in, it still put MySQL 3.something in...) When doing the configure above, I get this error: configure: error: Cannot find header files under /usr/include or whatever path I give it ... I'm having a hard time figuring out where the RPM puts everything, and an even harder time figuring out what path to stick to PHP ... Some detective work gave me these paths: MySQL (bins): /usr/bin /usr/share/mysql MySQL daemon (mysqld): /usr/libexec /usr/sbin MySQL headers (.h): /usr/include/mysql I've tried them all, but they all result in the above error. Anyone care to guess which path I should give to the configure? Or is it something else that causes this? I haven't ever used MySQL before, or any other SQL for that matter, so got 0 experience in getting the system up and running with it... TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
