[is it possible to use LAST_INSERT_ID () across queries]

use SET to store the insert id between queries.

 

SET @question_id = last_insert_id();

INSERT INTO question_option (question_id, question_option_id, creation_date, last_updated_date, question_option_text, question_option_correct) VALUES   
( @question_id, 1, NOW(), NOW(), '', 1),
( @question_id, 2, NOW(), NOW(), '', 0),	
( @question_id, 3, NOW(), NOW(), '',0),
( @question_id, 4, NOW(), NOW(), '', 0);

[Find Duplicate Entries in Table]

Seperates each value returned by the group by clause with a newline character \n - escape the newline character by with an additional backslash thus: \\n

[[SOLVED] Encrypting a mysql table?]

have 2 salts and 1 hash..

when they login use

md5($_POST['pass']+salts1) = user login password

to decrypt data use

md5($_POST['pass']+salts2)  = data password (store in session)

i would use 2 salts to make it a little more secure

 

does that make sense ?

 

No... but then again I do not really need to understand.

 

What I initially understood was that the encrypted password would be used as the key string in des_encrypt thusly:

 

SELECT @key_string := md5('secure_password');

UPDATE the_table SET the_field = des_encrypt('Some sensitive information stored in the database', @key_string);

SELECT des_decrypt(the_field, @key_string) FROM the_table

[[SOLVED] How to change data in the next row from an auto-increment table?]

If there are no gaps in the auto_increment ids, something like this may work:

SELECT @id:=id FROM table WHERE id = ( SELECT id + 1 FROM table WHERE marker = 1 );
UPDATE table SET marker = 0 WHERE marker = 1;
UPDATE table SET marker = 1 WHERE id = @id;

[[SOLVED] ID count type]

SELECT lpad(id, 6, 0) AS id FROM table;

[Find Duplicate Entries in Table]

Maybe escaping the newline will stop the error you get in TOAD.. and yeah, replace() only affects the copy of the string that is returned and not the actual stored data.

[[SOLVED] Encrypting a mysql table?]

Then one way encryption (md5/sha1) on the password would not be possible since decrypting would be required when applying the password as a salt to other information stored in a database.

[[SOLVED] Encrypting a mysql table?]

probably got some holes in that idea but still, its a thought

Yeah - the salt would have to be the encrypted password which is stored in plain text for a hacker to use as a salt against the encrypted information, the only thing stopping a hacker would be the lack of knowledge that the information is salted with the encrypted password.

[[SOLVED] Encrypting a mysql table?]

Use DES_DECRYPT and DES_ENCRYPT - but this form of encryption is unlikely to stop a hacker.

[Getting the highest value, then adding 1]

mysql_insert_id()

[[SOLVED] ID count type]

I do not quite understand - maybe set the column to zerofill.

[JOINs problem]

SELECT * FROM members
     LEFT JOIN member-group ON members.member_id = member-group.member_id 
WHERE member-group.member_id IS NULL

[DELETING FROM 2 TABLES]

hide your usename and password in the mysq_connect!!!!!

 

at a glance, albumid should be quoted here:

$albumid = $_POST[albumid];

[DELETING FROM 2 TABLES]

how didn't it work? - did you get a mysql error? or no records removed?

 

Use a LEFT JOIN so that the album record is removed regardless of whether or not there is a connecting song record.

 

EDIT: also be sure that $albumid contains a value or a current record id.

[[SOLVED] Problem Adding records]

no prob - mark this thread as solved.

[[SOLVED] Problem Adding records]

use addslashes() or mysql_real_escape_string() on the $element values.

 

i.e. $vendor = mysql_real_escape_string($element[3]);

 

[DELETING FROM 2 TABLES]

DELETE album, song
FROM album
     INNER JOIN song ON album.albumid = song.albumid 
WHERE album.albumid = $albumid

[DELETING FROM 2 TABLES]

There is no visible join in the above WHERE clause

 

DELETE FROM album, song USING album, song WHERE album.albumid = song.albumid AND albumid = $albumid 

 

[[SOLVED] Problem Adding records]

I do not understand these two seperate calls to the file() function.

 

<?php

// Load file and add it to the array $lines
echo "Loading File...";
$lines = file('http://www.tylerbiscoe.com/phptest/datafeeds/products.txt');
echo "Done. <br /> <br />";

// Insert the data into the database
echo "Inserting Data...";

$lines = file('http://www.tylerbiscoe.com/phptest/datafeeds/products.txt');

?>

 

Place a count straight after the above code to see how many lines are returned:

<?php

exit("<PRE>\n\nThe number of lines: ".sizeof(lines)."\n\n</PRE>");

?>

 

consider changing this:

 

<?php

foreach ($lines as $line_num => $line) {

$element[$line_num] = explode("|", $lines[$line_num]);


$vendor = $element[$line_num][3];
$itemName = $element[$line_num][1];
$itemNumber = $element[$line_num][0];
$catergory = $element[$line_num][21];
$spicture_url = $element[$line_num][5];
$lpicture_url = $element[$line_num][6];
$description = $element[$line_num][11];
$price = $element[$line_num][14];
$status = $element[$line_num][18];
$link = $element[$line_num][4];

?>

 

to this:

 

<?php

foreach ($lines as $line) {

$element = explode("|", $line);


$vendor = $element[3];
$itemName = $element[1];
$itemNumber = $element[0];
$catergory = $element[21];
$spicture_url = $element[5];
$lpicture_url = $element[6];
$description = $element[11];
$price = $element[14];
$status = $element[18];
$link = $element[4];

?>

 

and finally - print out each insert to the screen to be sure that each insert is there and not failing at the mysql level.

[Need help correcting query]

According to the PHP manual mysql_let_fields is deprecated - use mysql_query("SHOW COLUMNS FROM table"); instead, or simply DESC table, something like this:

 

<?
print "</PRE>";

$result = mysql_query("DESC table");

while ($record = mysql_fetch_assoc($result)) 
{
     print $record['Field]."\n"; // prints a column name
}

print "<PRE>";
?>

[[SOLVED] Help, How to change batch of *.bmp to *.jpg in database]

Strictly speaking, that's not very robust, since that substring could appear anywhere in the products_image string (although unlikely) -- hence my suggestion of a regex.

 

Yes, off coarse fenway - I recently developed a habit of naming images with dot seperators like btn.edit.gif so an image named btn.giffer.gif would end up being named btn.jpgfer.jpg - but as you suggest, unlikely.

 

MySQL really should apply full regular expressions so something like UPDATE table SET value = REGEXP '/(.+)\.gif$/\1.jpg/i' could be performed.

[What's wrong with this code?]

There does not appear to be anything syntatically wrong with the ALTER query provided - what was the error message?

[[SOLVED] Help, How to change batch of *.bmp to *.jpg in database]

Use the UPDATE statement (backup the table first):

 

UPDATE zen_products SET products_image = replace(products_image, '.gif', '.jpg')

[[SOLVED] Finding larger of 2 columns?]

You can also use the following (if SALE must be zero instead of null)

 

SELECT if (SALE = 0, RETAIL, SALE)

[[SOLVED] Help, How to change batch of *.bmp to *.jpg in database]

SELECT replace(col_name, '.gif', '.jpg') FROM tab_name;