Some MySQL Questions

[alexjewell]

I have several fields that are being dropped into a mysql database. These are the fields:

Img, Title, Description, Size1, Size2, Medium, Year

Img is a number and I want to check to see if it already exists in the table, and if it does to just replace those fields rather than add the information on? For example, if Img 37 already exists and all this information is submitted for 37, I want 37's information (already in the table) to be edited rather than a another row of information be added on, thus repeating 37 in the table when I'd like it to only appear once.

I also want to know if there's any particular way HTML should be handled with MySQL because Description will have HTML when submitted. I would like the HTML to be saved along with the field and I'm wondering if that causes any issues, like added backslashes and stuff.

[boogyman]

I have several fields that are being dropped into a mysql database. These are the fields:

Img, Title, Description, Size1, Size2, Medium, Year

image = the id given to the image?
title / description = one of those being alt one being title?
size 1 / size 2 = width and height?
medium = ???
year = ???

Img is a number and I want to check to see if it already exists in the table, and if it does to just replace those fields rather than add the information on? For example, if Img 37 already exists and all this information is submitted for 37, I want 37's information (already in the table) to be edited rather than a another row of information be added on, thus repeating 37 in the table when I'd like it to only appear once.

that is very confusing can you explain it more using a html example ? and remember to wrap your code in [code] tags

I also want to know if there's any particular way HTML should be handled with MySQL because Description will have HTML when submitted. I would like the HTML to be saved along with the field and I'm wondering if that causes any issues, like added backslashes and stuff.

you are talking about 2, no 3 different languages. PHP / MYSQL both have their special wildcards, so you really just need to add the appropriate modifications before evaluating with that languages... now on that note, unless you are inserting html as a whole into the database you could really create different fields in your image table and populate them with the values...

example

<img src="/images1.jpg" width="100" height="30" alt="This is the first Image" title="This is the first image">

in your image database table you could have

TABLE images
    img_id
    img_url
    img_width
    img_height
    img_description
    img_title

so you would check to see if the url exists in the table and if it does you would update the rest of the fields. if no you would create a new record putting the values of the fields in place

INSERT INTO images VALUES('','/image1.jpg',100,30,'This is the first Image','This is the first Image');

then when you are grabbing the information from php you would parse it soemthing like

<?php
_connect_
$res = mysql_query("SELECT * FROM images WHERE img_id = '___'");
if( $row = mysql_fetch_array($res) )
{
?>
       <img src="<?php echo $row['img_url']; ?>" width="<?php echo $row['img_width']; ?>" height="<?php echo $row['img_height']; ?>" alt="<?php echo $row['img_description']; ?>" title="<?php echo $row['img_title']; ?>">
<?php }
... rest of your code...
?>

which would be shown on the page back as

<img src="/images1.jpg" width="100" height="30" alt="This is the first Image" title="This is the first image">

[alexjewell]

Ok, let me explain a bit more about the situation. It's for an artist and each piece of artwork carries that information:

Img : the number associated with the image, used to display it in the gallery. For example, gal.php?img=37 will display the 37th image, 37.jpg

Title : the title displayed for the artwork. For example, he did a painting of Mick Jagger...so the title for that painting is Jagger and it's shown above the image.

Description : the painting's description, where the html comes in. Because the information will be added and edited by the client, the paragraph(s) for the description contains <p> tags, as well as other simple tags like <i> and <b>.

Size1 and Size2 would show as the following, in inches: 35" x 49". It's simply more information displayed with the image.

Medium : the medium he used for the painting...for example, oil on canvas or ink and graphite.

Year : the year the painting was completed


Now, my first question relates to when they add this information to the database. There is a form containing all of the fields above. Say they go to add 20. 20 already exists in the database...but the way it works is if there's a repeat, it just adds ANOTHER 20. I want to know if there's a way to just make the new 20 and its information REPLACE the 20 already in there and its information, thus preventing any repeats...as there should only be ONE of each image/number.

The second question is one I no longer need to worry about, as the way I have it seems to be working fine.

Thanks for your help.

[boogyman]

Img : the number associated with the image, used to display it in the gallery. For example, gal.php?img=37 will display the 37th image, 37.jpg

Okay... The picture itself does not need to be 37.jpg for example. That is what I was trying to tell you.

Title : the title displayed for the artwork. For example, he did a painting of Mick Jagger...so the title for that painting is Jagger and it's shown above the image.

Description : the painting's description, where the html comes in. Because the information will be added and edited by the client, the paragraph(s) for the description contains <p> tags, as well as other simple tags like <i> and <b>.

okay I got this wrong. I still would not allow html scripting tags as a malicious user could submit a script that could possibly comprimise the host. Instead to as most "forum" based sites and use the square brackets [] then you could process less/greater than signs as you wish (or not at all).

Size1 and Size2 would show as the following, in inches: 35" x 49". It's simply more information displayed with the image.

I would suggest using width / height attributes then put in a field for type of measurement, eg Inches, centimeters, etcetc... (and put those in a separate table and use a reference number in the images table.)

Medium : the medium he used for the painting...for example, oil on canvas or ink and graphite.

Depending on how many different medium combinations the artist uses, I would suggest creating another table of mediums and put the detail about them in there then once again using a reference number in the image.

Now, my first question relates to when they add this information to the database. There is a form containing all of the fields above. Say they go to add 20. 20 already exists in the database...but the way it works is if there's a repeat, it just adds ANOTHER 20. I want to know if there's a way to just make the new 20 and its information REPLACE the 20 already in there and its information, thus preventing any repeats...as there should only be ONE of each image/number.

when you create the admin script to deal with the image processing after submission, check to see if the image id already exists (eg get it from the url) but your url could look something like

// new submission
http://domain.com/gallery.php?do=new

// edit current submission
http://domain.com/gallery.com?do=edit&img=20

then after you gather the information and validate it you could do a check to insert it into the database

<?php
_validation_

$query = ($_POST['do']=='new') ? "INSERT INTO gallery VALUES ('[COLOR="Blue"]id[/COLOR]', _info_)" : "UPDATE gallery SET VALUES($_POST['img'], _info_) WHERE img_id = $_POST['img']";

?>

where id is left blank, and img_id is the name of the id field and it's set to auto-increment

that will take are of which query you need to do, so you would only need to worry about sanitizing the $_POST[] variables which like a good developer you would do before referencing them

[alexjewell]

Hey, it's for a client and nobody but that client will have access to it. It wouldn't make sense, either, that the client would upload something to harm their own server...and the CMS is, of course, password protected. Because of this, such level of security is not really needed. Thanks for the help though.

[boogyman]

still you should NEVER trust something from a client side.... because they might not do it maliciously but they can still do something unintentionally.... like putting a dot . which in mysql terms means a wildcard that can stand for ANY character