MySQL更新某些数据库字段而不会覆盖字段不变 [英] MySQL Updating some database fields without overwriting fields not changed

问题描述

好吧，这个让我发疯了.我有一个后端文件上传器，可将.jpg文件上传到服务器.然后，我想将.jpgs的文件名上传到我的数据库.因此，当页面加载时，我可以从数据库中添加文件名，图片将显示在页面上.这可以正常工作，但我还需要能够更新数据库中的文件和文件名.如果用户更改了所有文件和文件名，则一切正常.但是，如果用户只希望更改一个或两个文件和文件名，则MySql update语句最终会使某些变量为空，从而有效地删除了记录中的现有文件名，而不是单独保留它们.像往常一样，在寻求帮助之前，我已经搜索了stackoverflow和google，但没有发现任何真正相关的东西.这是适用的代码.

Ok, this one is driving me nuts. I have a backend file uploader that uploads .jpg files to the server. Then I want to upload the filename(s) of the .jpgs to my database. So when the page loads I can add the filename from the database and the pictures will display on the page. This works fine, but I also need to be able to update the files and the filenames in the database. If the user changes all the files and file names everything is fine. But if the user wishes to change only one or two file(s) and filename(s) the MySql update statement ends up having some of the variables empty thereby effectively deleting the existing filenames in the record instead of leaving them alone. As usual I have searched stackoverflow and google before asking for help and I have not found anything that is really pertinent. Here is the applicable code.

<?php 
 session_start();
 $id = $_SESSION['id'];

 //This is the directory where images will be saved 
 $target = "imgs/"; 
// "http://www.surfcup.com/travel_site/images/ ";

 $targetlogo = $target . basename( $_FILES['imageLogo']['name']);
 $targetpic1 = $target . basename( $_FILES['image1']['name']);
 $targetpic2 = $target . basename( $_FILES['image2']['name']);
 $targetpic3 = $target . basename( $_FILES['image3']['name']);
 $targetpic4 = $target . basename( $_FILES['image4']['name']);
 $targetpic5 = $target . basename( $_FILES['image5']['name']);

 //This gets all the other information from the form 

 $logo=($_FILES['imageLogo']['name']); 
 $pic1=($_FILES['image1']['name']); 
 $pic2=($_FILES['image2']['name']); 
 $pic3=($_FILES['image3']['name']); 
 $pic4=($_FILES['image4']['name']); 
 $pic5=($_FILES['image5']['name']); 


 // Connects to Database 
 mysql_connect("localhost", "surfcup_HotAdmin","password") or die ('I cannot connect to        the database because: ' .mysql_error());
 mysql_select_db("surfcup_hotels") or die('I cannot connect to the database because: .mysql_error());

 $query="UPDATE Hotels
 SET 
 hotel.imageLogo = '".$logo."',
 hotel.image1 = '".$pic1."',
 hotel.image2 = '".$pic1."',
 hotel.image3 ='".$pic1."',
 hotel.image4 = '".$pic1."',
 hotel.image5 = '".$pic1."'
 WHERE Hotels.id='".$id."'";


 mysql_query($query) or die ('Error Updating Hotel '.mysql_error());

//stuff to upload the files below



?>

我想我要么需要检查变量是否为null，要么以某种方式不加载它们，或者停止数据库接受null条目.不过，后者会使用户在创建记录时必须添加6个文件.如果他们只有5或3怎么办?我似乎无法理解如何检查变量是否为null并仅在UPLOAD语句中上载带有文件名的变量.再次感谢您的所有帮助. 戴夫

I think I either need to check if the variables are null and somehow not up load them or stop the database from accepting null entries. The later though would make the user have to add 6 files when he/she creates a record. What if they only had 5 or 3? I can't seem to get my head around how I would check if the variables are null and only upload the ones with filenames in them in the UPLOAD statement. Thanks again, in advance, for all your help. Dave

推荐答案

您可以尝试一下.基本上，它检查值是否为空.如果不是，则将值添加到数组.最后，我们将数组内嵌到一个字符串中，该字符串将添加到您的查询中.在该示例中，我只做了一些图像，但是您应该明白这一点.除非我的代码中出现语法错误，否则它应该可以工作.

You can try this. Basically it checks if the value is empty. If it is not, then it adds the value to the array. At the end we implode the array into a string that we will add to the your query. In the example I only did a few of the images, but you should get the point. It should work barring syntax errors in my code.

尽管我会说这不是最好的方法.您绝对可以对此进行改进，使其更安全，更有效.

Although I will say that this is not the best way to do it. You can definitely improve on this and make it more secure and efficient.

$uploaded_images = array();

if(!empty($logo)){
   $uploaded_images[] = "hotel.imageLogo = '".$logo."'";
}

if(!empty($pic1)){
   $uploaded_images[] = "hotel.image1 = '".$pic1."'";
}

if(!empty($pic2)){
   $uploaded_images[] = "hotel.image2 = '".$pic2."'";
}

$values_to_set = implode(', ', $uploaded_images);
$query= "UPDATE Hotels SET " . $values_to_set . " WHERE Hotels.id='" . $id . "'";

这篇关于MySQL更新某些数据库字段而不会覆盖字段不变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！