Including other hash-functions
Hi, as you know, MD5 and SHA1 are more or less broken so I guess it is time to think about including other hash-functions in MySQL. First of all it would be sensible to include SHA256 / SHA512, because they are designed a bit different to SHA1 and though are more safe. Another issue is to include completely new algorithms like tiger and whirlpool. Is there any possibility to do this without touching MySQL's source and if not, are there any plans to include them? Greets, Lars -- Kriterium des Wahren ist nicht seine unmittelbare Kommunizierbarkeit an jedermann -- Theodor Wiesengrund Adorno, aus: »Negative Dialektik« name: Lars H. Strojny web: http://strojny.net street: Engelsstraße 23blog: http://usrportage.de city: D-51103 Köln mail/jabber: [EMAIL PROTECTED] f-print: 1FD5 D8EE D996 8E3E 1417 328A 240F 17EB 0263 AC07 signature.asc Description: Dies ist ein digital signierter Nachrichtenteil
Re: query problem
On Wed, 8 Mar 2006 10:12:22 - [EMAIL PROTECTED] wrote:
snip one column select query
but I have two other filters which may or may not be chosen. (area, and
interest).
$query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND
area='area' AND interest='interest' ORDER BY fname $type;
but what if nether is chosen, or only one? Is there an AND/OR operator or
similar in mysql?
Your app needs to build the query.
In my libsql.php file I have:
function andclause($qry, $fld, $val, $op='=') {
$fmt = %s %s $op '%s';
$qry .= sprintf($fmt,
( preg_match('!\bWHERE\b!mi', $qry) ? 'AND' : 'WHERE'), $fld, $val);
return $qry;
}
With this, you can construct your initial query:
$qry = SELECT * FROM foo WHERE blah LIKE '$baz%';
// then test, case by case, to see if you need more selection clauses:
if (! empty($area))
$qry = andclause($qry, 'area', $area);
if (! empty($interest))
$qry = andclause($qry, 'interest', $interest);
echo 'span class=ddt', $qry, '/span';
$res = SQLQuery($qry);
...
Have fun.
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can we convert shape files
Hi all, Is there any possibility to convert and store a shape file into the mysql database like postgis feature in postgres.If there any please give me the steps how to convert or the link that consists of this documentation. Thanks in advance, Regards, venu.
Re: can we convert shape files
yes. on linux it is possible to convert shp files you have to use libmygis avaliable at http://jcole.us/software/libmygis/ shann ___ Halloween Humour: Why did Dracula divorce his wife after 800 years? postmaster.co.uk http://www.postmaster.co.uk/cgi-bin/meme/quiz.pl?id=157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Foreign Key
This is a question onto defining foreign keys in a relational database.
Foreign keys are featured by the InnoDB engine and therefore all three
tables of the database use it:
1. `friends` main table
2. `relation` deploys a simple relation between rows of the main table,
'1-2' means '1 is friend of 2'
3. `category` table with two columns, integer primary key and referring
text explanation. It is for distributing persons of the `friends` table
into categories.
All three tables have an id column defined as smallint(5) unsigned NOT
NULL auto_increment as their first column. (that is trying to be used to
establish the FOREIGN KEY definitions)
Here is the output of the mysql client:
mysql SHOW CREATE TABLE friends\G
*** 1. row ***
Table: friends
Create Table: CREATE TABLE `friends` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`firstname` varchar(22) collate latin1_german2_ci default NULL,
`lastname` varchar(30) collate latin1_german2_ci default NULL,
`nick` varchar(20) collate latin1_german2_ci default NULL,
`birthdate` date default NULL,
`gender` enum('m','f') collate latin1_german2_ci default 'm',
`category` smallint(5) unsigned default '0',
`phonehome` varchar(30) collate latin1_german2_ci default NULL,
`phonework` varchar(30) collate latin1_german2_ci default NULL,
`phonemobile` varchar(30) collate latin1_german2_ci default NULL,
`email` varchar(38) collate latin1_german2_ci default NULL,
`street` varchar(38) collate latin1_german2_ci default NULL,
`town` varchar(28) collate latin1_german2_ci default NULL,
`zip` smallint(5) unsigned default NULL,
`country` char(3) collate latin1_german2_ci default NULL,
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TI
MESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `Name` (`lastname`,`firstname`)
) *ENGINE=InnoDB* DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE relation\G
*** 1. row ***
Table: relation
Create Table: CREATE TABLE `relation` (
`id` smallint(5) unsigned NOT NULL,
`idx` smallint(5) unsigned NOT NULL,
KEY `idx` (`idx`),
KEY `id` (`id`),
CONSTRAINT `relation_ibfk_1` FOREIGN KEY (`idx`) REFERENCES `friends`
(`id`),
CONSTRAINT `relation_ibfk_2` FOREIGN KEY (`id`) REFERENCES `friends`
(`id`)
) *ENGINE=InnoDB* DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE category\G
*** 1. row ***
Table: category
Create Table: CREATE TABLE `category` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` char(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) *ENGINE=InnoDB* DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
---
Now the following FOREIGN KEY definitions work, defining FOREIGN KEY
relations from `relation` to `friends`:
ALTER TABLE `relation` ADD FOREIGN KEY (id) REFERENCES friends(id);
ALTER TABLE `relation` ADD FOREIGN KEY (idx) REFERENCES friends(id);
---
Only the one defining a FOREIGN KEY out of the main table `friends` onto
`categories` does not work:
mysql ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES
category(id);
*ERROR 1452 (23000): Cannot add or update a child row*:
a foreign key constraint fails (`kontakte/#sql-73c_7`, CONSTRAINT
`#sql-73c_7_ibfk_1` FOREIGN KEY (`catego
ry`) REFERENCES `category` (`id`))
mysql
Does anyone see why this does not work? (Other than repeating the error
message?)
How do I get it to work?
Regards,
Andy, Germany
Re: SQL Foreign Key
Hello Andreas,
You're not telling us what version of MySQL you're using,
so I'll have to give it a shot in the dark ...
mysql SHOW CREATE TABLE friends\G
*** 1. row ***
Table: friends
Create Table: CREATE TABLE `friends` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`firstname` varchar(22) collate latin1_german2_ci default NULL,
`lastname` varchar(30) collate latin1_german2_ci default NULL,
`nick` varchar(20) collate latin1_german2_ci default NULL,
`birthdate` date default NULL,
`gender` enum('m','f') collate latin1_german2_ci default 'm',
`category` smallint(5) unsigned default '0',
create an index on the CATEGORY column and try again.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Foreign Key
1) Sorry for not giving you the version:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
*mysql SELECT VERSION();
+---+
| VERSION() |
+---+
| 5.0.17-nt |
+---+*
1 row in set (0.00 sec)
2) Further, please note that the following output, of course, shows the
outlook of the table `relation` after the FOREGIN KEY definitions:
mysql SHOW CREATE TABLE relation\G
*** 1. row ***
Table: relation
Create Table: CREATE TABLE `relation` (
`id` smallint(5) unsigned NOT NULL,
`idx` smallint(5) unsigned NOT NULL,
* KEY `idx` (`idx`),
KEY `id` (`id`),
CONSTRAINT `relation_ibfk_1` FOREIGN KEY (`idx`) REFERENCES `friends`
(`id`),
CONSTRAINT `relation_ibfk_2` FOREIGN KEY (`id`) REFERENCES `friends`
(`id`)
*) *ENGINE=InnoDB* DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Defining an index on the category column of the `friends` table makes no
difference.
mysql SHOW TABLES;
++
| Tables_in_kontakte |
++
| category |
| friends|
| relation |
++
3 rows in set (0.00 sec)
*mysql ALTER TABLE friends ADD INDEX (category);
Query OK, 1 row affected (0.27 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES
category(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint f
ails (`kontakte/#sql-714_7`, CONSTRAINT `#sql-714_7_ibfk_1` FOREIGN KEY
(`catego
ry`) REFERENCES `category` (`id`))*
mysql SHOW CREATE TABLE friends\G
*** 1. row ***
Table: friends
Create Table: CREATE TABLE `friends` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`firstname` varchar(22) collate latin1_german2_ci default NULL,
`lastname` varchar(30) collate latin1_german2_ci default NULL,
`nick` varchar(20) collate latin1_german2_ci default NULL,
`birthdate` date default NULL,
`gender` enum('m','f') collate latin1_german2_ci default 'm',
`category` smallint(5) unsigned NOT NULL,
`phonehome` varchar(30) collate latin1_german2_ci default NULL,
`phonework` varchar(30) collate latin1_german2_ci default NULL,
`phonemobile` varchar(30) collate latin1_german2_ci default NULL,
`email` varchar(38) collate latin1_german2_ci default NULL,
`street` varchar(38) collate latin1_german2_ci default NULL,
`town` varchar(28) collate latin1_german2_ci default NULL,
`zip` smallint(5) unsigned default NULL,
`country` char(3) collate latin1_german2_ci default NULL,
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `Name` (`lastname`,`firstname`),
* KEY `category` (`category`)
*) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
1 row in set (0.01 sec)
InnoDB Status after failing instruction:
*mysql SHOW INNODB STATUS;*
...
LATEST FOREIGN KEY ERROR
060312 13:40:40 Transaction:
TRANSACTION 0 7440, ACTIVE 0 sec, OS thread id 3048 inserting, thread
declared
nside InnoDB 499
mysql tables in use 2, locked 2
6 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 7, query id 29 localhost 127.0.0.1 root copy to tmp table
ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES category(id)
Foreign key constraint fails for table `kontakte/#sql-714_7`:
,
CONSTRAINT `#sql-714_7_ibfk_1` FOREIGN KEY (`category`) REFERENCES
`category`
(`id`)
*Trying to add in child table, in index `category` tuple:
DATA TUPLE: 2 fields;
0: len 2; hex 0001; asc ;; 1: len 2; hex 0001; asc ;;
*
But in parent table `kontakte/category`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 696e66696d756d00; asc infimum ;;
The further error message from InnoDB status is again quite meaningless
and seems to make no sense. I have not found anything on the forums to
the question. This is somehow amazing, since it' s a quite simple
constellation that I am trying to establish here. Does someone really
understand the InnoDB error message and what's going wrong? It is not a
name clash either, for I tried giving the `category` column another name.
Andy Krueger
Martijn Tonies wrote:
Hello Andreas,
You're not telling us what version of MySQL you're using,
so I'll have to give it a shot in the dark ...
mysql SHOW CREATE TABLE friends\G
*** 1. row ***
Table: friends
Create Table: CREATE TABLE `friends` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`firstname` varchar(22) collate latin1_german2_ci default NULL,
`lastname` varchar(30) collate latin1_german2_ci default NULL,
`nick` varchar(20) collate latin1_german2_ci default NULL,
`birthdate` date default NULL,
`gender` enum('m','f') collate latin1_german2_ci default 'm',
`category` smallint(5)
Query help
I have 3 tables
Table 1: user(id, name, surname)
Table 2: crime(id, detail)
Table 3: user_crime(id, user_id, crime_id)
Table 1
1, OKAN, ARI
Table 2
1, detail 1
2, Detail 2
Table 3
1, 1, 1
1, 1, 2
So user 1 takes 2 crime from crime table...
I want to receive info with 1 query: I need name surname and his crime_ids
of user_id 1
So result might be:
user_id, name, suername, crime_ids
1, OKAN, ARI, {1-2}
Is it possible?
gibi
Mumkun mu?
-
Iletisim teknolojilerinin gucunu ispatlayan iletisim teknolojileri dergisi:
Tele.com.tr
http://www.tele.com.tr
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Foreign Key
Andreas Krüger wrote: 1) Sorry for not giving you the version: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. *mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 5.0.17-nt | +---+* 1 row in set (0.00 sec) snip InnoDB Status after failing instruction: *mysql SHOW INNODB STATUS;* ... LATEST FOREIGN KEY ERROR 060312 13:40:40 Transaction: TRANSACTION 0 7440, ACTIVE 0 sec, OS thread id 3048 inserting, thread declared nside InnoDB 499 mysql tables in use 2, locked 2 6 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 7, query id 29 localhost 127.0.0.1 root copy to tmp table ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES category(id) Foreign key constraint fails for table `kontakte/#sql-714_7`: , CONSTRAINT `#sql-714_7_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) *Trying to add in child table, in index `category` tuple: DATA TUPLE: 2 fields; 0: len 2; hex 0001; asc ;; 1: len 2; hex 0001; asc ;; * But in parent table `kontakte/category`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 696e66696d756d00; asc infimum ;; The further error message from InnoDB status is again quite meaningless and seems to make no sense. I have not found anything on the forums to the question. This is somehow amazing, since it' s a quite simple constellation that I am trying to establish here. Does someone really understand the InnoDB error message and what's going wrong? It is not a name clash either, for I tried giving the `category` column another name. Andy Krueger The foreign key cannot be created because there is at least one row in friends with a value for category which does not exist as an id in table category. In general, you cannot create a constraint that is already violated. Try this: SELECT f.id, f.category FROM friends f LEFT JOIN category c ON f.category=c.id WHERE c.id IS NULL; You will get a list of problem rows in table friends. Either change them to point to existing categories, or add matching records to table categories. Then you should be able to add the foreign key. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Commit question
Hi, we have a db with myisam and a single innodb table. 2 separate processes are inserting data into the myisam tables and the innodb table at the same time. We have noticed an issue where commits to the innodb table appear to be delayed until the process inserting into the myisam tables is finished. Has anyone else noticed this behavior? What could be causing this? Any help appreciated Rob Brooks The Brian Group LLC
Re: Query help
OKAN ARI wrote:
I have 3 tables
Table 1: user(id, name, surname)
Table 2: crime(id, detail)
Table 3: user_crime(id, user_id, crime_id)
Table 1
1, OKAN, ARI
Table 2
1, detail 1
2, Detail 2
Table 3
1, 1, 1
1, 1, 2
So user 1 takes 2 crime from crime table...
I want to receive info with 1 query: I need name surname and his
crime_ids of user_id 1
So result might be:
user_id, name, suername, crime_ids
1, OKAN, ARI, {1-2}
SELECT u.name, u.surname, uc.crime_id, c.detail
FROM user AS u
INNER JOIN user_crime AS uc ON u.id=uc.user_id
INNER JOIN crime AS c ON uc.crime_id=c.id
WHERE u.id=1
PB
Is it possible?
gibi
Mumkun mu?
-
Iletisim teknolojilerinin gucunu ispatlayan iletisim teknolojileri
dergisi: Tele.com.tr
http://www.tele.com.tr
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Foreign Key
Thanks Michael, that' s the problem. Syntactically my attempt was correct. Only I had the *value '1' in the only row that table `friends`contains, whereas table `category`was still empty*. (I was about creating the database for sample purposes.) When I tried to create the FOREIGN KEY out of friends(category) referencing category(id), MySQL detected that the value '1' is not contained in `category`. In other words, MySQL didn' t allow the definition for the integrity of the relational database. Adding a single row (with automatic id value '1') to `category` allows for the definition of the FOREIGN KEY allright: *mysql SELECT f.id, f.category - FROM friends f - LEFT JOIN category c ON f.category=c.id - WHERE c.id IS NULL; ++--+ | id | category | ++--+ | 1 |1 | ++--+ 1 row in set (0.09 sec) mysql INSERT INTO category (name) VALUES (School friends); Query OK, 1 row affected (0.03 sec) mysql ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES category(id); Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0 *mysql Sincerely, Andy Krueger Michael Stassen wrote: Andreas Krüger wrote: 1) Sorry for not giving you the version: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. *mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 5.0.17-nt | +---+* 1 row in set (0.00 sec) snip InnoDB Status after failing instruction: *mysql SHOW INNODB STATUS;* ... LATEST FOREIGN KEY ERROR 060312 13:40:40 Transaction: TRANSACTION 0 7440, ACTIVE 0 sec, OS thread id 3048 inserting, thread declared nside InnoDB 499 mysql tables in use 2, locked 2 6 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 7, query id 29 localhost 127.0.0.1 root copy to tmp table ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES category(id) Foreign key constraint fails for table `kontakte/#sql-714_7`: , CONSTRAINT `#sql-714_7_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) *Trying to add in child table, in index `category` tuple: DATA TUPLE: 2 fields; 0: len 2; hex 0001; asc ;; 1: len 2; hex 0001; asc ;; * But in parent table `kontakte/category`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 696e66696d756d00; asc infimum ;; The further error message from InnoDB status is again quite meaningless and seems to make no sense. I have not found anything on the forums to the question. This is somehow amazing, since it' s a quite simple constellation that I am trying to establish here. Does someone really understand the InnoDB error message and what's going wrong? It is not a name clash either, for I tried giving the `category` column another name. Andy Krueger The foreign key cannot be created because there is at least one row in friends with a value for category which does not exist as an id in table category. In general, you cannot create a constraint that is already violated. Try this: SELECT f.id, f.category FROM friends f LEFT JOIN category c ON f.category=c.id WHERE c.id IS NULL; You will get a list of problem rows in table friends. Either change them to point to existing categories, or add matching records to table categories. Then you should be able to add the foreign key. Michael
Checking for good update
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line
49
What code should I use to check if the update worked or not?
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Checking for good update
Perhaps reading the manual would be a good start at
http://us3.php.net/manual/en/function.mysql-num-rows.php
It clearly states
Retrieves the number of rows from a result set. This command is only
valid for SELECT statements. To retrieve the number of rows affected by
a INSERT, UPDATE, or DELETE query, use mysql_affected_rows().
You are using a function not valid for an UPDATE
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: fbsd_user [mailto:[EMAIL PROTECTED]
Sent: Monday, 13 March 2006 8:00 AM
To: Mysql
Subject: Checking for good update
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line
49
What code should I use to check if the update worked or not?
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Checking for good update
All ready read that and its clear as mud.
It tells you update is different but gives no example.
I asked in my post what should I code for checking the result of a
update.
-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED]
Sent: Sunday, March 12, 2006 4:50 PM
To: [EMAIL PROTECTED]; Mysql
Subject: RE: Checking for good update
Perhaps reading the manual would be a good start at
http://us3.php.net/manual/en/function.mysql-num-rows.php
It clearly states
Retrieves the number of rows from a result set. This command is only
valid for SELECT statements. To retrieve the number of rows affected
by
a INSERT, UPDATE, or DELETE query, use mysql_affected_rows().
You are using a function not valid for an UPDATE
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: fbsd_user [mailto:[EMAIL PROTECTED]
Sent: Monday, 13 March 2006 8:00 AM
To: Mysql
Subject: Checking for good update
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line
49
What code should I use to check if the update worked or not?
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
// build the query - (that's OK)
$query = UPDATE members SET email_verified='X' WHERE
logon_id=' .$logonid. ';
// send the query to the server - save the result resource
$res = mysql_query($query);
// test for the result of the above query
if(!$res)
{
// stop the script if the result is not valid
die('Query couldn\'t be executed:'.mysql_error());
}
// process a valid result
$row = mysql_fetch_array($res)
if (mysql_num_rows($res) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.
Maybe you need to get a simple introductory book on php, such as O'reillys
php pocket reference, ISBN 0596-00402-8.
Regards
Keith
In theory, theory and practice are the same;
In practice they are not.
On Sun, 12 Mar 2006, fbsd_user wrote:
To: Mysql mysql@lists.mysql.com
From: fbsd_user [EMAIL PROTECTED]
Subject: Checking for good update
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line
Probably because you are not sending a valid query to the server,
you will not be getting a valid result resource back from the server.
What code should I use to check if the update worked or not?
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Debuggers
I recently discovered that all my websites were wrecked. I spent several hours trying to troubleshoot it, until I finally discovered that my database was missing. I asked my webhost about it, and they told me they deactivated it because it was overwhelming the server. Someone told me about a software program called Mytop that can be used to debug MySQL. However, it looks way too complex for me. I just wondered if anyone on this list is aware of other, more user-friendly MySQL debuggers. Thanks. - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: Checking for good update
Maybe I need to read the copy of php pocket reference I have
to David - LOL.
Keith
On Sun, 12 Mar 2006 [EMAIL PROTECTED] wrote:
To: mysql@lists.mysql.com
From: [EMAIL PROTECTED]
Subject: Re: Checking for good update
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
// build the query - (that's OK)
$query = UPDATE members SET email_verified='X' WHERE
logon_id=' .$logonid. ';
// send the query to the server - save the result resource
$res = mysql_query($query);
// test for the result of the above query
if(!$res)
{
// stop the script if the result is not valid
die('Query couldn\'t be executed:'.mysql_error());
}
// process a valid result
$row = mysql_fetch_array($res)
if (mysql_num_rows($res) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.
Maybe you need to get a simple introductory book on php, such as O'reillys
php pocket reference, ISBN 0596-00402-8.
Regards
Keith
In theory, theory and practice are the same;
In practice they are not.
On Sun, 12 Mar 2006, fbsd_user wrote:
To: Mysql mysql@lists.mysql.com
From: fbsd_user [EMAIL PROTECTED]
Subject: Checking for good update
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line
Probably because you are not sending a valid query to the server,
you will not be getting a valid result resource back from the server.
What code should I use to check if the update worked or not?
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Debuggers
Is this a free hosting package David B? That's a bit naughty I think - just removing your database without warning you about it first. At least you would have had time to make a backup copy. Keith In theory, theory and practice are the same; In practice they are not. On Sun, 12 Mar 2006, David Blomstrom wrote: To: mysql@lists.mysql.com From: David Blomstrom [EMAIL PROTECTED] Subject: MySQL Debuggers I recently discovered that all my websites were wrecked. I spent several hours trying to troubleshoot it, until I finally discovered that my database was missing. I asked my webhost about it, and they told me they deactivated it because it was overwhelming the server. Someone told me about a software program called Mytop that can be used to debug MySQL. However, it looks way too complex for me. I just wondered if anyone on this list is aware of other, more user-friendly MySQL debuggers. Thanks. - Yahoo! Mail Use Photomail to share photos without annoying attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Debuggers
[EMAIL PROTECTED] wrote: Is this a free hosting package David B? That's a bit naughty I think - just removing your database without warning you about it first. At least you would have had time to make a backup copy. * * * * * I'm paying $50 a month, and I was blown away when they shot me down like that. I complained about it on their forum, and they said they had no choice - it was an emergency. But what was stopping them from contacting me AFTER they yanked my database? At any rate, with my peak season about six weeks away, I'm now shopping for a new webhost. It's been so long since I researched webhosts, I've discovered terms I wasn't even aware of, like VPS. It looks like I can get a much better account for what I've been paying these guys. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Re: MySQL Debuggers
At 04:27 PM 3/12/2006, David Blomstrom wrote: I recently discovered that all my websites were wrecked. I spent several hours trying to troubleshoot it, until I finally discovered that my database was missing. I asked my webhost about it, and they told me they deactivated it because it was overwhelming the server. Someone told me about a software program called Mytop that can be used to debug MySQL. However, it looks way too complex for me. I just wondered if anyone on this list is aware of other, more user-friendly MySQL debuggers. Thanks. David, Who was the ISP? Maybe they'd like some free advertising. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Debuggers
David, Who was the ISP? Maybe they'd like some free advertising. :)Ha! I'd better wait until April 1, when I hope to have a new home. I've actually been promoting this webhost since I've been with them. They just flaked out very badly all of a sudden. Coincidentally, they had a major server crash about the very time they dumped my database. In fact, that's one reason it took me so long to troubleshoot it - I assumed I had merely been affected by the server crash, and their were so many angry customers on the forum, I was leery of adding to the congestion. Now if you want to roast a webhost, try the infamous Dathorn. I was blasting the owner of that operation years ago. When I posted about my current situation on WebHostingTalk.com, I discovered another Andrew flame-fest. Sheez. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
RE: Checking for good update
Maybe I have the overall logic wrong.
I don't do a select query first to see if that record is there.
I just try to update it.
If the logonid key in the update request is on the table, the record
does get updated. I can verify that using phpmyadmin.
When the logonid key in the update request is not on the table, the
results checks still take the record successful updated condition. A
look at the table using phpmyadmin shows me that there is no record
matching that logonid key.
My last test I tried this
if ($results == TRUE)
and still the update was successful condition is taken even when the
update key value is not on the table. I would expect the update was
unsuccessful condition to have been taken.
So the basic question boils down to why does the successful
condition always get taken even when there is no match on the table
for the key value being used?
This is testing a new developed script, so there may be a logic
error in how things are done in the script. But I need to have the
results of the update to be able to tell difference between a good
update and one where the key used to target the record is not on the
table.
How do you suggest I should code the result condition test?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, March 12, 2006 5:20 PM
To: mysql@lists.mysql.com
Subject: Re: Checking for good update
Maybe I need to read the copy of php pocket reference I have
to David - LOL.
Keith
On Sun, 12 Mar 2006 [EMAIL PROTECTED] wrote:
To: mysql@lists.mysql.com
From: [EMAIL PROTECTED]
Subject: Re: Checking for good update
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
// build the query - (that's OK)
$query = UPDATE members SET email_verified='X' WHERE
logon_id=' .$logonid. ';
// send the query to the server - save the result resource
$res = mysql_query($query);
// test for the result of the above query
if(!$res)
{
// stop the script if the result is not valid
die('Query couldn\'t be executed:'.mysql_error());
}
// process a valid result
$row = mysql_fetch_array($res)
if (mysql_num_rows($res) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
I've not tested this - but it looks like you are mixing sending
the
mysql query and testing for the result of the query at the same
time,
which AFAIK is not possible.
Maybe you need to get a simple introductory book on php, such as
O'reillys
php pocket reference, ISBN 0596-00402-8.
Regards
Keith
In theory, theory and practice are the same;
In practice they are not.
On Sun, 12 Mar 2006, fbsd_user wrote:
To: Mysql mysql@lists.mysql.com
From: fbsd_user [EMAIL PROTECTED]
Subject: Checking for good update
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid
MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on
line
Probably because you are not sending a valid query to the server,
you will not be getting a valid result resource back from the
server.
What code should I use to check if the update worked or not?
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
fbsd_user wrote:
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line 49
What code should I use to check if the update worked or not?
Your second line does just that.
$query = UPDATE members SET email_verified='X' WHERE
logon_id='.$logonid.';
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
Right there. You told php to die and print the error from mysql if the update
failed.
if (mysql_num_rows($result) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
The rest of the code only makes sense for a select, but you did an update.
mysql_num_rows() tells the number of rows of data returned by a select. No
select, no returned rows, no mysql_num_rows() -- hence the error message.
Logan, David (SST - Adelaide) wrote:
Perhaps reading the manual would be a good start at
http://us3.php.net/manual/en/function.mysql-num-rows.php
It clearly states
Retrieves the number of rows from a result set. This command is only
valid for SELECT statements. To retrieve the number of rows affected by
a INSERT, UPDATE, or DELETE query, use mysql_affected_rows().
You are using a function not valid for an UPDATE
fbsd_user wrote:
All ready read that and its clear as mud.
It tells you update is different but gives no example.
I asked in my post what should I code for checking the result of a
update.
Please reread David's post, as he gave you the answer in the quote from the
manual. Use mysql_affected_rows() to get the number of rows affected by an
UPDATE. There are a few caveats, however. See the manual for details
http://www.php.net/manual/en/function.mysql-affected-rows.php.
fbsd_user wrote:
Maybe I have the overall logic wrong.
I think perhaps you do.
I don't do a select query first to see if that record is there.
I just try to update it.
Then it will be difficult to know for certain, in that scenario, whether or not
a row was matched.
If the logonid key in the update request is on the table, the record
does get updated. I can verify that using phpmyadmin.
When the logonid key in the update request is not on the table, the
results checks still take the record successful updated condition. A
look at the table using phpmyadmin shows me that there is no record
matching that logonid key.
That's right. Your query is
UPDATE members SET email_verified='X' WHERE logon_id=$logonid;
You are asking mysql to set the email_verified column to 'X' for every row in
the table which has the given logon_id. The success of this query is not
determined by the number of rows matched. If no rows match, mysql will
successfully update 0 rows (just as it will successfully update 13 rows, if
13 rows match). That is not an error, as it is precisely what you requested.
My last test I tried this
if ($results == TRUE)
and still the update was successful condition is taken even when the
update key value is not on the table. I would expect the update was
unsuccessful condition to have been taken.
No. The query worked. It successfully updated all 0 matching rows.
So the basic question boils down to why does the successful
condition always get taken even when there is no match on the table
for the key value being used?
Because success of a query does not depend on the existence of rows which match
its WHERE clause. Success depends on parsing and executing the query.
This is testing a new developed script, so there may be a logic
error in how things are done in the script. But I need to have the
results of the update to be able to tell difference between a good
update and one where the key used to target the record is not on the
table.
How do you suggest I should code the result condition test?
As David suggested, you can use mysql_affected_rows() to find how many rows were
affected by your update. This will certainly be 0 if there is no matching row.
You need to be aware, however, that it will also be zero if the matching
row(s) already has email_verified='X', because mysql will not waste time
changing a row for which the new value is the same as the old value. If that's
a possibility, you could try parsing the outut of mysql_info(). See the manual
for details http://www.php.net/manual/en/function.mysql-info.php.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
[EMAIL PROTECTED] wrote:
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
Why? There's nothing wrong with the above statement.
// build the query - (that's OK)
$query = UPDATE members SET email_verified='X' WHERE
logon_id=' .$logonid. ';
// send the query to the server - save the result resource
$res = mysql_query($query);
// test for the result of the above query
if(!$res)
{
// stop the script if the result is not valid
die('Query couldn\'t be executed:'.mysql_error());
}
Fine so far, but the code below repeats the problem. There are no rows to
process, because there was no SELECT.
// process a valid result
$row = mysql_fetch_array($res)
if (mysql_num_rows($res) == 1)
{
// the user id and password match,
print(User id on db);
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print(Sorry, wrong user id / password);
}
I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.
You should try it. It works just fine, and isn't the problem. The problem is
that you cannot treat the result of an UPDATE as if it were a SELECT.
snip
Using this code I get this error message.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line
Probably because you are not sending a valid query to the server,
you will not be getting a valid result resource back from the server.
The query was valid, and it worked, but mysql_num_rows() is only for SELECTs.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to Log Warnings and Errors from queries
Hello Ryan. I am more of a developer than a MySQL administrator. Hence, I would always favour applications logging query errors rather than being dependent on MySQL to generate a log for me. Of course, I may be wrong. You could write a query execution function, say exec_mysql_query(...) in one of you files, say 'Db.inc' and have it included in all your files. exec_mysql_query(...) will log all mysql errors into a file. and you may provide an web-interface (assuming this is a web application) to view/download the log files. Regards, Rithish. -Original Message- From: ryan lwf [mailto:[EMAIL PROTECTED] Sent: Friday, March 10, 2006 8:14 PM To: Dan Nelson Cc: mysql@lists.mysql.com Subject: Re: How to Log Warnings and Errors from queries Hi Dan, Noted with thanks. As such, is there a workaround to log problematic sql queries ran against the mysqld server ? Do I need to write separate script to do this ? Regards, Ryan. On 3/10/06, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Mar 08), ryan lwf said: I understand that the option log-errors and log-warnings only logs server related internal errors. How do I enable logging errors from queries executed, so that I can fix the problematic query statement accordingly? The statement SHOW WARNINGS and SHOW ERRORS does not work on my server with mysqld-4.0.25 binary version. Those commands appeared in MySQL 4.1. Before then, warnings were simply counted. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
