Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Wednesday 11 December 2013

Recursive delete for blogs and forums

Let's say, you've developed a blog or forum and you need to delete some entries. You will also want to delete the responses to those entries. In this example we'll use a table structure like this, where 'refid' is a field used to associate a response with it's referer :
`id`
`refid`
`userid`
`title`
`content`
`tags`
`created`
In our PHP we want to go through each blog entry which we'd like to delete and ascertain it's responses identified by 'refid' before deleting it. Thus :
function deleterecursiveblog($id)
  {
    $q = "SELECT * FROM `blog` WHERE `refid`='{$id}'";  
    $result = mysqli_query($con,$q);
    if(isset($result))
    {
      foreach($result as $key)
      {
        deleterecursiveblog($key['id']);  
      }
    }
    $q = "DELETE FROM `blog` WHERE `id`='{$id}'";  
    $result = mysqli_query($con,$q);
  }

Tuesday 2 October 2012

Presenting MySQL table data through PHP, JSON and jQuery

This may seem like a long way round to do something, but believe me, it has its benefits if you are trying to deliver an open systems approach to your development framework.

Let's say I have a MySQL database with table called chat. The chat table has 3 fields; userid, entry and datesaved.

I create a PHP script called jsonout.php which returns all rows of the chat table, thus:

<?php
$con = mysql_connect('localhost','root','');
mysql_select_db('test', $con);
$result = mysql_query('SELECT * FROM `chat`');
while($rows[] = mysql_fetch_assoc($result));
array_pop($rows);
mysql_close($con);
echo json_encode($rows);
?>

Notice the final line (in red) where I return a JSON encoded version of the resulting array.

Now my HTML page (below) can collect the JSON through jQuery and present the results on the page.


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Simple JSON retrieval through jQuery</title>
<!--[if IE]>
 <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<link rel="stylesheet" href="http://meyerweb.com/eric/tools/css/reset/reset.css" />
<style>
body
{
 font:10px/15px Sans-serif;
}
</style>
<script src="http://www.google.com/jsapi"></script>
<script>
 google.load("jquery", "1");
</script>
</head>
<body>
<script>
(function()
{
 $.getJSON('jsonout.php', function(data)
 {
    $.each(data, function(i, items)
    {
      $('body').append('<p>'+items.userid+'</p>'+items.entry+'<p>'+items.datesaved+'</p>');
    });
 });
})();
</script>
</body>
</html>

Tuesday 28 August 2012

Saving and retrieving images using MySQL through PHP

I really should have looked into this a long time ago, but still. The example below deliberately doesn't use jQuery. If you want to POST files through jQuery, you'll need to use an ajax form plugin.

Right. Now we've got that out of the way, to my example. I have created a database in MySQL with a table called pictures, and I have 2 fields:
id : int(8)

picture : longblob
I have used the database class which I developed earlier to handle the requests.
At this stage, I'm just handling JPEG images.


<?php
require_once 'database.class.php';
$db = new database;
if($_POST)
{
$image=$_FILES['uploadfile']['tmp_name'];
$fp = fopen($image, 'r');
$content = fread($fp, filesize($image));
$content = addslashes($content);
fclose($fp);
$sql="insert into pictures (picture) values ('$content')";
$results = $db->query($sql);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>File upload</title>
</head>
<body>
<form enctype="multipart/form-data" method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" name="MAX_FILE_SIZE" value="10000000" />
<input name="uploadfile" id="uploadfile" type="file" />
<input type="submit" value="Submit" />
</form>
<div id="results">
<?php
$arr = $db->query('SELECT * FROM pictures');
if($arr)
{
foreach($arr as $row)
{
echo $row['id'];
echo '<img src="data:image/jpeg;base64,'.base64_encode($row['picture']).'" /><br />';
}
}
?>
</div>
</body>
</html>

Enjoy!

Tuesday 13 December 2011

Invalid field count in csv input on line 1

I came across this annoying phpmyadmin 'feature' when trying to import some data to a newly created MySQL table. I had a CSV file full of data. The columns matched the number of fields but I kept getting the error 'Invalid field count in csv input on line 1'.

The problem was, that the 'Fields terminated by' field in the import screen was set to ';' instead of a ','. By resetting this field, everything worked.

Monday 5 December 2011

Reset the Index of a MySQL table

Sometimes, I will be playing about with a MySQL database, often at the start of an application. I'll fire a load of dummy data in and test. When I've finally done with this phase I'm ready to start from the beginning. I might have an ID field which I'd like to auto increment but this time starting from 0. To reset this field, first empty all the records in the table, then you can apply a line like this:
ALTER TABLE `mytable` AUTO_INCREMENT=0

Monday 7 November 2011

Getting single row results MySQL using PHP

I'm always using this code. Sometimes I know there should be/is only one record returned from my MySQL query. Especially if I have specified 1 result as in the query below. As long as I know the resulting column names, this is a very useful technique.

<?php

$query = mysql_query("SELECT * FROM `users` WHERE `username` = '{$username}' LIMIT 1;");
$row = mysql_fetch_assoc($query);
echo 'Hello '.$row['firstname'];
?>