MYSQL :: Aes_encrypt Values

Using AES_ENCRYPT For 256 Bit Encryption
I just started using AES_ENCRYPT to encrypt records in my databases. For the most part, the default 128 bit encryption is fine. But, I would like to use 256 bit for one of my forms. According to the MySQL documentation you can use the function for either 128 or 256 but I don't understand how to get it to do 256.

The documentation says:"Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source." - http://dev.mysql.com/doc/refman/4.1/...functions.html

What source do I have to modify to get it to use 256?

AES_ENCRYPT Function
Essentially I am playing around with the aes_encrypt fucntion to securly store some data in the db. The problem I am having is that I get the following error : Data truncated for column 'foo' at row 1 and the table creating was done with:

CREATE TABLE `foobar` (
`foo` varchar(10000) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

with a varchar(10000) and doing and AES_ENCRYPT('foo','bar') one would think that it would work. But I suspect that I am doing something incorrectly.

AES_ENCRYPT And AES_DECRYPT
I have recently updated my system from using MySQL 4.0 to MySQL 5.0. The thing is, none of the encrypted fields are being decrypted correctly when using Connector/J. I did get the newest version of it but still no luck.

Query Error (AES_ENCRYPT)
I havae a user database set up where the passwords are encrypted using AES_ENCRYPT with the date the user joined being the salt for each user. The inserts work fine for the password, but for some reason I'm getting an error on this statement below when I try to update....

update users set password = AES_ENCYPT('123456', '2005-09-15 22:11:06') where user_id = '806'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('123456', '2005-09-15 22:11:06') where user_id = '806'' at line 1

Any idea what is going on here?

BTW... the password field is set up as a BLOB.

Aes_encrypt Aes_decrypt Problem
I'm using PHP 4 with mySQL. I'm using aes_encrypt and _decrypt to store customer information as securely as possible. The problem crops up when I go to store (or retrieve -- I'm not sure which) the state that the customer lives in.

AL works for Alabama
GA works for Georgia
fl works for Florida
FL returns an empty string.

Does anyone have any ideas on what's happening here?

AES_ENCRYPT In Visual Basic
I'm writing an app with vb & mysql.

for the user login - i tried aes_encrypt and vb reports function or sub not defined.

How To Calculate The Size Of An AES_ENCRYPT() Result
what I want is to calculate the result string for an AES_ENCRYPT() function, in the mysql manula says:the result string length may be calculated as 16*(trunc(string_length/16)+1) but I dont know what value trunc has.

Could any one please explainme how to calculate the max size of an 10 chars max wide password value?

Get All Values
Is it possible to just return all the values that are in a column?

Two WHERE Values
I dont even know what to search for on this. Is it at all possible to have two WHERE values on a query? Would I maybe put an and symbol or something? what is the proper syntax for something like this, or is it even possible?

SELECT * FROM Hotlines WHERE date_opened='20050203'

First And Last Values
Is this the most efficient way of performing this action?

SELECT
(SELECT `Date` FROM mytable ORDER BY `Date` LIMIT 1) AS FirstDate,
(SELECT `Date` FROM mytable ORDER BY `Date` DESC LIMIT 1) AS LastDate

Sum Of Values
I'm working on something that I don't have much experience with. I have the
following query:

select item_no, sum(price), count(*) from production where date > '2006-11-15' group by item_no;

which returns:

item_no --- sum(price) --- count(*)
27714 --- 327.6 --- 6
29582 --- 269.64 --- 4
38599 --- 476.34 --- 6

Is there a way that I can get the total of the sum(price)? Do I need to do
this in PHP?

Top 2 Values
I have a table called Labs and a table called EE's. Each EE has many labs in the lab table. I am trying to figure out how I find the top 2 labs (based on date) and return that along with all of the fields in the EE table. Would I use a sub query?
Something like this? SELECT * FROM ee, WHERE ee.id in (select top 2 from labs). This didn't seem to work.

Max Values
is it possible to set a maximum value for a column?
I want to have a max value of 100 so if the current value is 98 and the query tells it to update by a value of 3, it will still update it but only up the maximum.

In Between Values
i got a table

[code]
type points

...
4 100
5 200
6 300
...

now..lets say i got 150 pts...so that will fall to type 4... so whats the proper sql statement for that?

Getting Values From Row
One way that I determine the most recent insertion into a table is to
get the highest id value in the id field. Using the auto increment
function whenever a new record is inserted in the table, the hightest
id number by default is the most recent submission into this table.
I would like to create a query that pulls up all the data from the row
with the highest id number. I know that to get that number, I can use
the max(id) approch, but I don't know how to enter this into a sql
statement, and get the other values that attach to it. I tried "select
name from table where id=max(id) but I get a group error reply.
What can I do to get the values I'm looking for?

Get Rid Of Values
This is it, say i have six numeric fields to fill in.
What i need is to get rid of the biggest and smallest numbers among these six fields, so i can take into account just the middle four left fields. It's a system for ranking persons performances.

For example:
field#1= 6.3
field#2= 2.3
field#3= 7.5
field#4= 4.9
field#5= 8.3
field#6= 4.5

and i need to get rid of the biggest and lowest entered values, in this case, i should get rid of fields #2 and #5

Sum Values
Let's say I have a messages table with a points column, where points may be 0, 1, 2 or 3.
Is there a way to selectively sum the values for each id except for points equal to 3 ?
The query below sums everything, even when points equals 3.

SELECT id, SUM(points) AS total_points, COUNT(*) AS num_messages FROM messages GROUP BY id

Total Sum Of Values?
this isn't a question of "how do I do this" but more of a "what's the best efficient way of doing this"

the current code is: $result=mysql_query("SELECT votes FROM list");
votes is always a numerical value

what's the most efficient way to add the total value of the votes?

Trigger Old And New Values
I have two tables with some fields.

RealTableName
ID int
FieldOne varchar
FieldTwo varchar

OldValuesTableName
ID
RealTableNameID
FieldOne
FieldTwo

I want to create a trigger which transfers the current values of the `RealTableName` record I am updating to the `OldValuesTableName`. This should happen before update.

Returning All Values
I'm sure this is a straight forward and obvious one:

I have this:

SELECT *
FROM tbl_contacts
WHERE con_Customer = "#URL.con_Customer#"
Which obviously when you pass a value over the url returns the relevant records.

How can I pass a value to the url which passes ALL the records back.
Is it something like ?con_customer='%'?

Get The Enum Values
how can I get the variable that enum field can take it? like if I have Sex field that could take (male,female) .

I need something like that:

select (all the values for the sex field) ; //output (male,female).

Setting All Values To 0 And Some To 1
Is there a single query I can run that will set all values of a single column to 0, but set some values to 1?

somthing like:

How Do I Get Values From Select?
I need to assign values pulled by a select query to varibles, but the only thing I know how to do with the values I get from a select query is make a table. Below is the code that puts the values I need stored in variables in a table:

$query = "SELECT title, description, height, width FROM images WHERE filename = '$imagepath'";
$result = mysql_query($query)
or die (mysql_error());
echo "<TABLE BORDER=1>";
WHILE ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "<tr>";
WHILE ( list ($key, $value) = each($row) )
{
echo "<TD>" . $value . "</TD>";
}
echo "</TR>";
}
echo "</TABLE>";

Its designed to call only one row so I just need the four values called in the query to have their own variable,

Getting Values From Row With The Largets Id
One way that I determine the most recent insertion into a table is to
get the highest id value in the id field. Using the auto increment
function whenever a new record is inserted in the table, the hightest
id number by default is the most recent submission into this table.

I would like to create a query that pulls up all the data from the row
with the highest id number. I know that to get that number, I can use
the max(id) approch, but I don't know how to enter this into a sql
statement, and get the other values that attach to it. I tried "select
name from table where id=max(id) but I get a group error reply.

Ordering Values
How do i order numbers retrieved by there value, such as: 1,2,3,4,5...12,13...22 so its like that instead of 1,11,12,13,14,2,21,22,23,24,31,4,5,6...

128 Bit Values As Primary Key
Suppose that 'bigkey' is a column capable of representing the value of
a 128-bit unsigned integer in some efficient fashion... maybe as a
CHAR or VARCHAR field big enough to hold its decimal or hex
representation, or maybe some other datatype, and ${128bitvalue} is a
representation of it acceptable to MySQL.

Suppose 'smallkey' is an UNSIGNED INTEGER column.

Which of the following two queries is likely to be faster and more
efficient?

SELECT somecolumn, anothercolumn FROM foo, bar WHERE foo.bigkey =
${128bitvalue} AND bar.bigkey = ${128bitvalue}

SELECT somecolumn, anothercolumn FROM main, foo, bar WHERE main.bigkey
= ${128bitvalue} AND foo.smallkey = main.smallkey AND bar.smallkey =
main.smallkey

If MySQL had a native 128-bit integer type, it would probably be a
no-brainer to just use it directly. Unfortunately, as far as I know,
there's no way to store a 128-bit integer in a MySQL column without
converting it into some alternate representation first.

Assuming that I'm still better off using the 128 bit value's
representation directly as the key instead of joining it via a third
table, what's the most efficient way to represent that 128 bit value
to MySQL? CHAR(39)? VARCHAR(39)? Some other format?

Between Values Of 2 Columns
Consider the following table:

FROM_HERE-----TO_HERE-----VALUE
1--------------------10--------------------a
11--------------------20--------------------b
21--------------------30--------------------c

How could I get the VALUE(a,b or c) of a $number that is between FROM_HERE and TO_HERE?

For example $number = 17; 17 is between 11 and 20, so VALUE = b.

(Note that 11 and 20 must be included, so if we have $number = 11 or $number = 20 -> VALUE must be "b")

Retrieving Values From Url
i want to put the value in a variable but dont know how to retrieve it:

i want to use record_num = 3 as a variable:

URL: .../review.php?,record_num=3

EXAMPLE OF VAR

$link_id ='[record_num]';

SOURCE
<a href='review.php?,record_num={$row['link_id']}'>LINK</a>

Values NOT In Range
I want to find all values for a field NOT in a range.

In this case, I want to see which membersIDs are NOT used say between 1000-1000.

Finding the used ones is dead easy, but I can't find a simple way (besides creating a list of all numbers between 1000 and 10000) to get the ones that are NOT used.

Grouping Only Top 10 Values
I am trying to figure out how to run something complex like this:

my table structure is similar to this:

player_id
player_points

each player has alot of rows with points.

The way we have our listings displayed is by doing a

SELECT player_id, SUM(player_points) as total GROUP BY player_id ORDER BY total DESC.

This works great but now we need to change our queries to reflect only top 10 scores of each player. So if a player has 50 entries for different points, we need to grab only the top 10 points and find the sum of that. Our table has over 20 million rows.

Can anyone guide me on how I would go on doing this? One way is by passing row by row into php and counting only top 10 rows. But this means going through all 20 million rows through php, very ineffective.

Adding Values?
How can i add the values of different rows in mysql using php?

ie:

id field!
1 3
2 2
3 4
4 2
5 6
6 20
7 7
8 5
n 1
-----------------
total = value of id#1 upto id# n.

for this example total should be = 50

Row Values COUNT
I'm creating database that has three rows Month, dtate and values. There I want to get count all values in some month rane. then I used query like this...

SELECT month, values ,COUNT(*) FROM table_test where month between month1 and month3 group by month

That query result was How many row in the that month period. But I want to count the values in the that row( values row).

Round Values
How do I round values in a view. Do I change the table? The type that needs rounding is currently formatted as double.

How To Get Different Values From The Column
I am reposting my question here as there's no reply from the other post (different subject). In the column, there are mixture of multiple same and different values.

How do I write to obtain all different values with the limited row? For example, select pet from pet_customers where ?????? order by Date Desc limit 25;

Find Value Not In Between Values.
I have a SQL question I am having trouble with. We have a table, with
a column that contains a code. These codes start at '00001', '00002',
'00003', '00004'... and so on. If the record containing '00003' as it's
code is deleted from the table, is there an SQL statement that can be
used to find the first missing record. I guess what we are looking for
is the lowest value of CODE not in the list of values between two
values, such as in the case '00001' to '99999'.

Add Values Into A Column
I have a column called year with the format YYYY-MM-DD, but I would like to have year, mount and day in three different columns in my database. How can I do this?

Get Min & Max Values Of A String
I have a field 'zip' in a table in my database that records values in the form on 'something/023/something', 'anything/346/somemore','kztt/1984/asdg'

I want to get the min and max values of this field in particular the numbers in the string. Initially, I simply run a query to get the min and max value of this field which works until the numbers in the string went to 4-digits instead of the usual 3.

Example query: select min(zip) as min, max(zip) as max from $dbtable

Then I run an explode function to retrieve the numbers in the string.

This query now return values like min=999, max=1000 instead, when the min number should be 023 and max is 1984

Modifying Row ID Values
I want to be able to delete a row in a table, and then have every row's ID from below that row to subtract one, in order to move up.

Here's how I have it, and I'm sure I'm doing something wrong.

$query = "DELETE FROM tbl_image WHERE id = '{$_GET['del']}'";
mysql_query($query) or die('Error : ' . mysql_error());

$query1 = "SELECT FROM tbl_image WHERE id > '{$_GET['del']}";
mysql_query($query1) or die('Error : ' . mysql_error());
$result2 = mysql_query($query1);

while($row = mysql_fetch_array($result2))
{
$id = $id - 1;

$query2 = "UPDATE tbl_image SET id = '$id'"
mysql_query($query2) or die('Error : ' . mysql_error());
}

Return Values

SELECT someNumber, COUNT(someSubject) FROM someTable WHERE (someNumber BETWEEN 10 AND 20) GROUP BY someNumber

The query returns the count for someSubject, grouped by someNumber providing at least one matching someSubject was found for each someNumber.

Where there were no matching someSubjects, the someNumber group is not returned.

Specifically, I am getting back the count for 16, 17, 18, 19 and 20.

Although the count for 10, 11, 12, 13 and 14 is zero, I still want this to display in the returned table.

Can any help to show me how I can force the BETWEEN values to display when the count for that value is zero?

Selecting MAX Values
I would like to select the MAX of a value as well as the corresponding value in another column on the same row. Let me try to explain below:

Name NO Runs
Test1 1 200
Test2 0 100
Test1 0 30
Test2 1 110
Test1 0 210
Test2 1 150

A simple SELECT Name MAX(Runs) AS MAX FROM Table GROUP BY Name would give me:

Name MAX
Test1 210
Test2 150

I would like to select the NO value that corresponds with the Max value. So the below is what I ideally would want

Name Max NO
Test1 210 0
Test2 150 1

Right now by querying with SELECT Name MAX(Runs) AS MAX, NO FROM Table GROUP BY Name - it is only pulling back the first NO value it comes across, not the value that corresponds with the MAX value and giving me the following:

Name Max NO
Test1 210 1
Test2 150 0

Any ideas anyone has about getting the corresponding NO value for the Max(Runs) value.

VALUES From Variables
im sure this is basic stuff, but i cant find anything on this yet...

$insert_query = "INSERT INTO `error`
( `company` , `location`, `employee`, `problem` ) VALUES
( 'x', 'y', 'z', 'a' ) ";

this is my code, but instead of x,y,z i need to put the value of my selected option. using php, i have them assigned to str1 -str4, how do i put those variables in as values?

Values From Different Rows
I have Orders and Payments. Sometimes the Orders do not have Payments. So, I
decided to use LEFT JOIN.
Furthermore, some Orders have 1 related Payment, and some Orders have 2 or
even more related Payments.
Now, I want to combine the sum of all Payments and the last Payment in one
row.

select Orders.SeqNr,
sum(B1.Amount) Total_paid,
max(B2.SeqNr) Last_record,
B2.Amount Last_amount
from Orders
left join Payments B1
on B1.FK_Orders = Orders.SeqNr
left join Payments B2
on B2.FK_Orders = Orders.SeqNr
group by Orders.SeqNr

If there is only 1 payment per order, then I will see perfect results.
However, if there is more than 1 payment per order, then the results are
wrong...
The column Last_amount does not contain the amount which belongs to the
column Last_record.

Split Values 12|3|43
I know what I need, but not sure how to do it.. I know it can be done as I;ve seen it before.

So all I'm after is a point in the right direction, perhaps to a tutorial that will explain to me how to:

a) Store values in a database field like: 12|3|43
b) How to retrieve these values as seperate variables.

Duplicate Values
today that as a result of some bad code somewhere a huge number of duplicates got inserted such that if I search for the relational id I come up with two of those records with the exact same value.
How could I search through the table to delete all but one of the duplicate records?
For instance, I have the following result set returned when I search for rel_id=45:
id rel_id value
-----------------------
15 45 some text
16 45 some text

Return All Values
On my user registration processing page I need to test the visitor's proposed login name against all registered login names to ensure uniqueness. My two MySQL books are unclear, so I have a couple of questions:
1) How do I compose a query that returns all the values in a single column (username, in this case), so that I can test my new username against them to ensure uniqueness before I write the new username to the db?

2) Does MySQL provide a quick and dirty way to do such a thing, or will I manually loop through all returned values to test for uniqueness?

Rounding Off Values
ive got this query where it get the results in long fractional numbers like 23.455555566663333. my query is somewhat like this

select namesite,class,count(distinct name),avg(}}col1{{),avg(}}col2{{) from xyz group by namesite, class.

is there any way i can round it up to the nearest integer value.

ENUM Values
Is there a way to get values for an ENUM field from another table in the database? What is the SQL syntax for that.In other words, I have a table called "products" which has a field called "category". The field category should be an ENUM composed only of values stored in the category table. Basically, can we use a SELECT statement within an ENUM description?

Default Values
I'm building a website, and a control panel along with it. The admins on the control panel will not all have full access. They get permission to do certain actions. These permissions are stored in the table crew_site_permissions.

The table crew_site_permissions has an index field (not auto_incremented) named crew_id. Whenever a new admin account is installed by one of the current admins, I want the site to automatically create the privileges. So the only thing I want to insert, is the new admin's ID. The rest of the fields (at least 30 others), should get their default value (1 or 0).