|
PHP - Blobs, Mysql And Php
Hello, first of, this is my first post.
I'm pretty new to PHP and MySQL but I'm learning (at least, I hope ;-) ) What I aim to do with my page is download a mediumblob from my database... Uploading is no problem, but downloading either gives me the blobfield as text back in my browser or tries to download is as download.php (which file is empty). What I aim to learn is: How do I get the page to download the file and what did I do wrong Hope somebody can help! Cheers, sjoerd Code to upload comes down to: <?php if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0) { $fileName = $_FILES['userfile']['name']; $tmpName = $_FILES['userfile']['tmp_name']; $fileSize = $_FILES['userfile']['size']; $fileType = $_FILES['userfile']['type']; $email = $_POST['email']; $voornaam = $_POST['voornaam']; $achternaam = $_POST['achternaam']; $fp = fopen($tmpName, 'r'); $content = fread($fp, filesize($tmpName)); $content = addslashes($content); fclose($fp); if(!get_magic_quotes_gpc()) { $fileName = addslashes($fileName); } include 'library/config.php'; include 'library/opendb.php'; $query = "INSERT INTO upload (name, size, type, content, email, voornaam, achternaam ) ". "VALUES ('$fileName', '$fileSize', '$fileType', '$content', '$email', '$voornaam', '$achternaam' )"; mysql_query($query) or die('Error, query failed'); include 'library/closedb.php'; echo "<br>File $fileName uploaded<br>"; } ?> Code for download <?php if(isset($_GET['id'])) { // if id is set then get the file with the id from database include 'library/config.php'; include 'library/opendb.php'; $id = $_GET['id']; $query = "SELECT name, type, size, content " . "FROM upload WHERE id = '$id'"; $result = mysql_query($query) or die('Error, query failed'); list($name, $type, $size, $content) = mysql_fetch_array($result); header("Content-Disposition: attachment; filename=\"$name\"\n"); header("Content-Length: .filesize($size)"); header("Accept-Ranges: bytes"); header("Pragma: no-cache"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-transfer-encoding: binary"); echo $content; include 'library/closedb.php'; exit; } ?> Similar TutorialsHello I have a database table containing users uploaded word docs as long blobs (I know in an ideal world these should be in a filesystem, not a table but that wasn't possible) I'd like to be able to generate an email with php and send out these files as attachments. I have pear mail installed and can get this to send out emails with attachments form a file share no problems. Getting it to work with blobs escapes me though. Is this possibile of do I need a different class? Code: [Select] include('../db_connection.php'); $query1 = "SELECT id, name, type, content, size FROM upload WHERE lref='" . $_POST['lref'] . "'"; $result2 = $mysqlilsr->query($query1) or die ($mysqlilsr->error); list($id, $name, $type, $content, $size) = $result2->fetch_array(); //echo $name . '<br/>'; //echo $content . '<br/>'; //echo $type . '<br/>'; //$fileattname = file_get_contents($_POST['attachment']); $fileattname = file_get_contents($_POST['attachment']); $from_mail = "email address"; $replyto = "email address"; //phpinfo(); //mail_attachment($filename, $path, $mailto, $from_mail, $from_name, $replyto, $subject, $message); $headers = array( 'From' => $from_mail, 'Subject' => $subject, 'Cc' => $replyto, ); $body = $header . $spacer .$message . $spacer . $footer; $mime = new Mail_mime(); $mime->setTXTBody($body); $mime->addAttachment(base64_encode($content), $name, $type, "iso-8859-1"); $body = $mime->get(); $headers = $mime->headers($headers); $mailer =& Mail::factory('mail'); $res = $mailer->send($_POST['email'], $headers, $body); if (PEAR::isError($res)) { echo 'error'; } else { 'success'; } Hey guys, I`m having problems finding information on how to insert a image on a blob column trough odbc on a oracle database. Does anyone knows where can i find it, or can anyone help me with sample code? I really need to use odbc functions (cant use oci8 ) Tanks for your time reading this and for the possible input you may add on this. Sincerely Arestas
create table mimi (mimiId int(11) not null, mimiBody varchar(255) );
<?php
//connecting to database
include_once ('conn.php');
$sql ="SELECT mimiId, mimiBody FROM mimi";
$result = mysqli_query($conn, $sql );
$mimi = mysqli_fetch_assoc($result);
$mimiId ='<span>No: '.$mimi['mimiId'].'</span>';
$mimiBody ='<p class="leading text-justify">'.$mimi['mimiBody'].'</p>';
?>
//what is next?
i want to download pdf or text document after clicking button or link how to do that Hello everyone, Sorry if this has been answered but if it has I can't find it anywhere. So, from the begining then. Lets say I had a member table and in it I wanted to store what their top 3 interests are. Their$ row has all the usual things to identify them userID and password etc.. and I had a further 3 columns which were labled top3_1 top3_2 & top3_3 to put each of their interests in from a post form. If instead I wanted to store this data as a PHP Array instead (using 1 column instead of 3) is there a way to store it as readable data when you open the PHPmyadmin? At the moment all it says is array and when I call it back to the browser (say on a page where they could review and update their interests) it displays 'a' as top3_01 'r' as top3_02 and 'r' as top3_03 (in each putting what would be 'array' as it appears in the table if there were 5 results. Does anyone know what I mean? For example - If we had a form which collected the top 3 interests to put in a table called users, Code: [Select] <form action="back_to_same_page_for_processing.php" method="post" enctype="multipart/form-data"> <input name="top3_01" type="text" value="enter interest number 1 here" /> <input name="top3_02" type="text" value="enter interest number 2 here" /> <input name="top3_03" type="text" value="enter interest number 3 here" /> <input type="submit" name="update_button" value=" Save and Update! " /> </form> // If my quick code example for this form is not correct dont worry its not the point im getting at :) And they put 'bowling' in top3_01, 'running' in top3_02 and 'diving' in top3_03 and we catch that on the same page with some PHP at the top --> Code: [Select] if (isset($_POST)['update_button']) { $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars $top3_02 = $_POST['top3_02']; // i.e, 'running' $top3_03 = $_POST['top3_03']; // i.e, 'diving' With me so far? If I had a table which had 3 columns (1 for each interest) I could put something like - Code: [Select] include('connect_msql.php'); mysql_query("Select * FROM users WHERE id='$id' AND blah blah blah"); mysql_query("UPDATE users SET top3_01='$top3_01', top3_02='$top3_02', top3_03='$top3_03' WHERE id='$id'"); And hopefully if ive got it right, it will put them each in their own little column. Easy enough huh? But heres the thing, I want to put all these into an array to be stored in the 1 column (say called 'top3') and whats more have them clearly readable in PHPmyadmin and editable from there yet still be able to be called back an rendered on page when requested. Continuing the example then, assuming ive changed the table for the 'top3' column instead of individual colums, I could put something like this - Code: [Select] if (isset($_POST)['update_button']) { $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars $top3_02 = $_POST['top3_02']; // i.e, 'running' $top3_03 = $_POST['top3_03']; // i.e, 'diving' $top3_array = array($top3_01,$top3_02,$top3_03); include('connect_msql.php'); mysql_query("UPDATE members SET top3='$top3_array' WHERE id='$id' AND blah blah blah"); But it will appear in the column as 'Array' and when its called for using a query it will render the literal string. a r r in each field instead. Now I know you can use the 'serialize()' & 'unserialize()' funtcions but it makes the entry in the database practically unreadable. Is there a way to make it readable and editable without having to create a content management system? If so please let me know and I'll be your friend forever, lol, ok maybe not but I'd really appreciate the help anyways. The other thing is, If you can do this or something like it, how am I to add entries to that array to go back into the data base? I hope ive explained myself enough here, but if not say so and I'll have another go. Thanks very much people, L-PLate (P.s if I sort this out on my own ill post it all here) I have following piece of code below, and I would like to be able to express it pure SQL, something that could go into a .sql file :
$request_string='SELECT topic_forum_id FROM topic_table WHERE topic_id= 2014';
$query=database->prepare($request_string);
$query->execute();
$data=$query->fetch();
$query->closeCursor();
$forum=$data['topic_forum_id'];
$request_string='INSERT INTO post_table
(post_topic,post_forum) VALUES (2014,:forum)';
$query=database->prepare($request_string);
$query->bindValue(':forum',$forum,PDO::PARAM_INT);
$query->execute();
$data=$query->fetch();
$query->closeCursor();
Is it possible ?
So i have this php as shown below. It should make a list of comments with comment replies below their comment respectively. The problem is that it only goes through and shows 1 comment and all the comment replies for that one comment. It should be showing all comments i have in the db for that article. If i remove the second while then it shows all the comments correctly but no comment replies then... How do i get this script to loop through the db for every comment but also loop through every comment reply for that $row[id]? If anyone has a better / more efficient way of what I am trying to do, please explain or show example (i am open to anything)... Code: [Select] // what article are we showing? $article_to_show_id = $_GET['article_id']; $active_is_set_text = "1"; // Active Column text that makes it okay to show // Finding the article $search_for_article = mysql_query("SELECT * FROM articles WHERE id = '$article_to_show_id' AND active = '$active_is_set_text'"); while($row = mysql_fetch_array($search_for_article)) { // format the last updated date right $update_date_edit = $row[update_date]; $update_date_edit = date('F j, Y \a\t h:ia', $update_date_edit); $row[update_date] = $update_date_edit; // format the submit updat date right $submit_date_edit = $row[submit_date]; $submit_date_edit = date('F j, Y \a\t h:ia', $submit_date_edit); $row[submit_date] = $submit_date_edit; echo ' <div> ', $row[title] ,' </div> <div> by: ', $row[author] ,' on ', $row[submit_date] ,' </div> <div> ', $row[content] ,' </div> <div> Last Updated: ', $row[update_date] ,' </div> <form action="article_reply.php" method="post"> <input type="hidden" name="article_id" value="', $row[id] ,'" /> <button name="article_reply" type="submit" value="submit">Reply</button> </form> '; } $comment_count = 0; $comment_reply_count = 0; // Finding all of the comments $search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '0'"); while($row_comment = mysql_fetch_array($search_for_article)) { // format the submit updat date right $comment_date_edit = $row_comment[comment_date]; $comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit); $row_comment[comment_date] = $comment_date_edit; echo ' <br> <br> COMMENT:<br> <div> By: ', $row_comment[username] ,' on ', $row_comment[comment_date] ,' </div> <div> ', $row_comment[comment] ,' </div> '; $comment_count++; // Finding all of the comment replies if any $search_for_article = mysql_query("SELECT * FROM article_comments WHERE article_id = '$article_to_show_id' AND reply_id = '$row_comment[id]'"); while($row_two = mysql_fetch_array($search_for_article)) { // format the submit updat date right $comment_date_edit = $row_two[comment_date]; $comment_date_edit = date('F j, Y \a\t h:ia', $comment_date_edit); $row_two[comment_date] = $comment_date_edit; echo ' <br> <br> COMMENT REPLY:<br> <div> By: ', $row_two[username] ,' on ', $row_two[comment_date] ,' </div> <div> ', $row_two[comment] ,' </div> '; $comment_reply_count++; } } Need some help I have 2 tables in a database and I need to search the first table and use the results from that search, to search another table, can this be done? and if it can how would you recommend that I go about it? Thanks For Your Help Guys! Hi all I am am trying to use the following piece of code to import a CSV file into a mySQL database: Code: [Select] $filename = $_FILES['sel_file']['tmp_name']; $handle = fopen($filename, "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { mysql_query("INSERT INTO user SET id = '".$userid."', name = '".$data[0]."', email = '".$data[1]."', phone = '".$data[2]."'") or die(mysql_error()); } fclose($handle); It works great except it only imports one line in the CSV file. How do I get it to keep adding the lines into the DB? My CSV looks like this: Pete Naylor,test@mail.com,0800101101 Bob Jones,bob@mail.com,08700123123 Many thanks for for help Pete I have a table that has a field called rating which can be from 0-100. I want to create a mysql query that will count the number of ratings between 0-20 then 21-40 then 41-60 etc. However I'm not really sure how to do this so any help would be great. Hi im having problems with this can any one point me the right way please Code: [Select] if(isset($_GET['pageID'])){ $id = $_GET['pageID']; } ?> <?php if(isset($_POST['submit'])){ $title = $_POST['title']; $keywords = $_POST['keywords']; $description = $_POST['description']; $menu = $_POST['menu']; $content = $_POST['content']; $query = mysql_query("UPDATE 'page' SET title='$title', keywords='$keywords', description='$description', menu='$menu', content='$content' WHERE 'pageID'= $id"); } ?> Hi all! I am very new to php and mysql world so I could need some help. I found this UCP php code to control a database, where we could find various userinfo. I edited it and for unknown reasons everything else works except the change password. Here is the NewPassword.php: Code: [Select] <html> <body> <form action="Newpass.php" method="post"> <b><font size="4" color="#000080">Change your password! </font></b> <p>Old password <input type="password" name="P1" size="20" /> </p> <p>New Password <input type="password" name="P2" size="20" /></p> <p>Confirm New Password <input type="password" name="P3" size="20" /></p> <p> <input type="submit" /> <input type="reset" /> </p> <a href="Logged.php"">Go back</a> </form> </body> </html>And here is the Newpass.php Code: [Select] <?php session_start(); $pass1 = $_POST["P1"]; $pass2 = $_POST["P2"]; $pass3 = $_POST["P3"]; $escpass1 = mysql_real_escape_string($pass3); $escpass2 = mysql_real_escape_string($pass3); $escpass3 = mysql_real_escape_string($pass3); $user = $_SESSION['Username']; if(!isset($_SESSION['Username'])) { echo('You are not logged in!'); echo '<a href="index.php"">Go back</a>'; die; } $escuser = mysql_real_escape_string($user); include("database.php"); $query = "SELECT * FROM playerinfo WHERE user = '$escuser'"; $result = mysql_query($query); $row = mysql_fetch_row($result); if (!$con) { die('Could not connect: ' . mysql_error()); } $username_exist = mysql_num_rows($result); if($username_exist == 0) { echo('That username does not exist'); echo '<a href="NewPassword.php"">Go back</a>'; die; } if($escpass1 !== $row[1]) { echo("Wrong old password!"); echo '<a href="New Password.php"">Go back</a>'; die; } if($escpass2 !== $escpass3) { echo("Your new passwords do not match!"); echo '<a href="NewPassword.php"">Go back</a>'; die; } $query = "UPDATE playerinfo SET password = '$escpass3' WHERE user= '$escuser'"; $result = mysql_query($query); echo 'Password Changed!'; $_SESSION['Password'] = $escpass3; echo '<a href="Logged.php"">Go back</a>'; ?>And database.php Code: [Select] <?php $con = mysql_connect("edit","edit","edit"); mysql_select_db("edit"); //Remember that if you are using a external source, change the login info (mysql_connect(server[],user[],pass[]); ?>I edited the user/pw for obvious reasons. So the problem itself: Whatever I insert in password box I get this in return: Code: [Select] Warning: mysql_real_escape_string(): Access denied for user ''@'localhost' (using password: NO) in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 6 Warning: mysql_real_escape_string(): A link to the server could not be established in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 6 Warning: mysql_real_escape_string(): Access denied for user 'crpnet'@'localhost' (using password: NO) in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 7 Warning: mysql_real_escape_string(): A link to the server could not be established in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 7 Warning: mysql_real_escape_string(): Access denied for user 'crpnet'@'localhost' (using password: NO) in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 8 Warning: mysql_real_escape_string(): A link to the server could not be established in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 8 Warning: mysql_real_escape_string(): Access denied for user 'crpnet'@'localhost' (using password: NO) in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 16 Warning: mysql_real_escape_string(): A link to the server could not be established in /home/crpnet/domains/c-rp.net/public_html/blankUCP/Newpass.php on line 16 That username does not existGo back Need help with php/sql. When I add a drink to the cart, only the quantity increases, but the item does not go to the cart. Check it out on my website http://fhcs.be/cart-demo2/ This is a part of my index.php <h1>Drinks</h1> <? $Link = mysql_connect("xxxx","xxxx","xxxx"); $Query = "SELECT * FROM products"; $DBName = "xxxx"; $results = mysql_db_query($DBName, $Query, $Link); ?> <select name = "drop1" size="1" id="drop1"> <Option Value=" ">Select Drink:</option> <? //Begins PHP for($u=0;$u<mysql_num_rows($results); $u++) { $id=mysql_result($results,$u,'name'); ?> <option value="<? echo($id); ?>"><? echo($id); ?></option><? //Begins PHP //Begins PHP } ?> </select> <? //Begins PHP if (mysql_db_query ($DBName, $Query, $Link)) { } else { print ("FAILURE<BR>\n"); } mysql_close ($Link); ?> <a href="cart.php?action=add&id='.$row['id'].'">Add to cart</a> //button to add to chart And this is a part of my cart.php <?php // Include MySQL class require_once('inc/mysql.class.php'); // Include database connection require_once('inc/global.inc.php'); // Include functions require_once('inc/functions.inc.php'); // Start the session session_start(); // Process actions $cart = $_SESSION['cart']; $action = $_GET['action']; switch ($action) { case 'add': if ($cart) { $cart .= ','.$_GET['id']; } else { $cart = $_GET['id']; } break; case 'delete': if ($cart) { $items = explode(',',$cart); $newcart = ''; foreach ($items as $item) { if ($_GET['id'] != $item) { if ($newcart != '') { $newcart .= ','.$item; } else { $newcart = $item; } } } $cart = $newcart; } break; case 'update': if ($cart) { $newcart = ''; foreach ($_POST as $key=>$value) { if (stristr($key,'qty')) { $id = str_replace('qty','',$key); $items = ($newcart != '') ? explode(',',$newcart) : explode(',',$cart); $newcart = ''; foreach ($items as $item) { if ($id != $item) { if ($newcart != '') { $newcart .= ','.$item; } else { $newcart = $item; } } } for ($i=1;$i<=$value;$i++) { if ($newcart != '') { $newcart .= ','.$id; } else { $newcart = $id; } } } } } $cart = $newcart; break; } $_SESSION['cart'] = $cart; Hey, I was wondering.. Is there any php functions or any way I can get some information from MySQL? I want to be able to echo out the uptime for MySQL, and some other things.. I've seen it done before so I know its possible lol. I am having issues in writing a program. The basic idea is a internet radio website. I have a page listing all shows on the station with a brief description. When you click on a specific show from that page, i need it to open a page showing more detail on just that show. The show list page is loading with no problem, but the show detail pages keep coming up with "Error in your sql syntax at line 1" See what I have at http://www.eliteweboffers.com/lwrs/shows.php Here is the code from config.php Code: [Select] <?php mysql_connect($host,$user,$password); mysql_select_db($db); //Functions function show_list() { $query = "SELECT * FROM shows"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)){ echo " <table border='0'> <tr> <td rowspan='2'> <a href='". $site ."show_page.php?". $row['id'] ."' name='show_id' value='". $row['id'] ."'> <img src='". $site,$row['picture'] ."' height='180' width='180'></img></a> </td> <td> " . $row['show'] . " </td> </tr> <tr> <td> " . $row['description-short'] . " </td> </tr> </table> ";} } function show_page() { $show_id=$_POST[show_id]; $query = "SELECT * FROM shows WHERE id = $show_id"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)){ echo " <table border='0'> <tr> <td rowspan='2'> <img src='". $site,$row['picture'] ."' height='180' width='180'></img> </td> <td> " . $row['show'] . " </td> </tr> <tr> <td> " . $row['description-short'] . " </td> </tr> </table> ";} } ?> If i change the line Code: [Select] $show_id=$_POST[show_id];to Code: [Select] $show_id=1; It loads the show detail page properly for the first show. I don't want to have to use "if" statements for every single show id. What am I doing wrong ? Can anyone help ??? Here is the code from shows.php Code: [Select] <?php include ('/home/ansonb/lwr_config.php'); show_list(); ?> Here is the code from show_page.php Code: [Select] <?php include ('/home/ansonb/lwr_config.php'); show_page(); ?> Any help would be greatly appreciated. Thank you ! Umm.. Yeah, this MySQL line needs some reviewing list($total_banned_servers) = mysql_fetch_row(mysql_query("SELECT COUNT(id) FROM `toplist` WHERE banned='1' && serverowner=". $_SESSION['user'] ."")); I have been trying very hard to get this script to work but I am no good at arrays... Anyhow, the basic idea is that the script will read an RSS feed and post all that content to the database which will be used to display content on the site. <?php $doc = new DOMDocument(); $doc->load('http://newsrss.bbc.co.uk/rss/sportonline_uk_edition/football/rss.xml'); $arrFeeds = array(); foreach ($doc->getElementsByTagName('item') as $node) { $itemRSS = array ( 'title' => $node->getElementsByTagName('title')->item(0)->nodeValue, 'desc' => $node->getElementsByTagName('description')->item(0)->nodeValue, 'link' => $node->getElementsByTagName('link')->item(0)->nodeValue, 'date' => $node->getElementsByTagName('pubDate')->item(0)->nodeValue ); array_push($arrFeeds, $itemRSS); } include 'dbconnect.php'; foreach ($arrFeeds as $item) { $sql="INSERT INTO tb_rss (title, desc, link, date) VALUES ($item => title), $item => desc, $item => link, $item => date)"; if (!mysql_query($sql)){ die('Error: ' . mysql_error()); } } ?> Current error messege is: Code: [Select] Error: 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 'desc, link, date) VALUES (Array => title), Array => desc, Array => link, Array =' at line 1 Help please? is it true ? in paging 0,92,085,0,50,066,0,73 etc its coming to mixing Quote $en_cokk = mysql_query("select baslik,baslik_seo , liked/(liked+hate) avg from hikayeler order by avg DESC limit 10"); there are two row in database liked and hate; how can i take average and sort desc to page from database ? Hi Chaps, I've got something of a mixed bag, in which I need some help with a MySQL query and PHP to display the results correctly. I'm basically after the following: Query to produce results of; invoice_item_price of all jobs that include a Typesetting task and all of those that don't. The problem I'm having is that I can't figure out how to get the correct SUMtotal of the invoice_item_price, when searching through the tbl_task_item, to see if a Typesetting task has been completed. In trying to do this, it multiplies the invoice_item_price by the amount of tasks within a job. Database: Code: [Select] -- MySQL dump 10.11 -- -- Host: localhost Database: dbtemp -- ------------------------------------------------------ -- Server version 5.0.83-community-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `tbl_analysis` -- DROP TABLE IF EXISTS `tbl_analysis`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_analysis` ( `analysis_id` int(20) unsigned NOT NULL auto_increment, `FK_job_id` int(20) unsigned default NULL, `analysis_gross` int(10) default NULL, `analysis_net` int(10) default NULL, PRIMARY KEY (`analysis_id`), KEY `FK_job_id` (`FK_job_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_analysis` -- LOCK TABLES `tbl_analysis` WRITE; /*!40000 ALTER TABLE `tbl_analysis` DISABLE KEYS */; INSERT INTO `tbl_analysis` VALUES (1,1,100,50),(2,2,500,250); /*!40000 ALTER TABLE `tbl_analysis` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_customer` -- DROP TABLE IF EXISTS `tbl_customer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_customer` ( `cust_id` int(20) unsigned NOT NULL auto_increment, `cust_name` varchar(255) default NULL, PRIMARY KEY (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_customer` -- LOCK TABLES `tbl_customer` WRITE; /*!40000 ALTER TABLE `tbl_customer` DISABLE KEYS */; INSERT INTO `tbl_customer` VALUES (1,'Customer A'); /*!40000 ALTER TABLE `tbl_customer` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_invoice` -- DROP TABLE IF EXISTS `tbl_invoice`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_invoice` ( `invoice_id` int(20) unsigned NOT NULL auto_increment, `FK_cust_id` int(20) unsigned default NULL, `FK_proj_id` int(20) unsigned default NULL, `invoice_date` date default NULL, `invoice_total` varchar(30) default NULL, `invoice_status` enum('Pending','Sent') default NULL, PRIMARY KEY (`invoice_id`), KEY `FK_cust_id` (`FK_cust_id`), KEY `FK_proj_id` (`FK_proj_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_invoice` -- LOCK TABLES `tbl_invoice` WRITE; /*!40000 ALTER TABLE `tbl_invoice` DISABLE KEYS */; INSERT INTO `tbl_invoice` VALUES (1,1,1,'2010-01-30','500','Sent'); /*!40000 ALTER TABLE `tbl_invoice` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_invoice_item` -- DROP TABLE IF EXISTS `tbl_invoice_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_invoice_item` ( `invoice_item_id` int(20) unsigned NOT NULL auto_increment, `FK_job_id` int(20) unsigned default NULL, `FK_invoice_id` int(20) unsigned default NULL, `invoice_item_price` varchar(30) default NULL, `invoice_status` enum('Pending','Sent') default 'Pending', PRIMARY KEY (`invoice_item_id`), KEY `FK_job_id` (`FK_job_id`), KEY `FK_invoice_id` (`FK_invoice_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_invoice_item` -- LOCK TABLES `tbl_invoice_item` WRITE; /*!40000 ALTER TABLE `tbl_invoice_item` DISABLE KEYS */; INSERT INTO `tbl_invoice_item` VALUES (1,1,1,'250','Sent'),(2,2,1,'250','Sent'); /*!40000 ALTER TABLE `tbl_invoice_item` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_job` -- DROP TABLE IF EXISTS `tbl_job`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_job` ( `job_id` int(20) unsigned NOT NULL auto_increment, `FK_proj_id` int(20) unsigned default NULL, `FK_langt_id` int(20) unsigned default NULL, `job_title` varchar(255) default NULL, `job_pages` int(10) default '1', `job_complete` enum('Yes','No') default 'No', `job_complete_date` timestamp NULL default NULL, PRIMARY KEY (`job_id`), KEY `FK_proj_id` (`FK_proj_id`), KEY `FK_langt_id` (`FK_langt_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_job` -- LOCK TABLES `tbl_job` WRITE; /*!40000 ALTER TABLE `tbl_job` DISABLE KEYS */; INSERT INTO `tbl_job` VALUES (1,1,1,'Job 1',1,'Yes','2012-30-01 17:30:00'), (2,1,3,'Job 2',2,'Yes','2012-30-01 17:30:00'); /*!40000 ALTER TABLE `tbl_job` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_language` -- DROP TABLE IF EXISTS `tbl_language`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_language` ( `lang_id` int(20) unsigned NOT NULL auto_increment, `langname` varchar(255) default NULL, PRIMARY KEY (`lang_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_language` -- LOCK TABLES `tbl_language` WRITE; /*!40000 ALTER TABLE `tbl_language` DISABLE KEYS */; INSERT INTO `tbl_language` VALUES (1,'EN'),(2,'DE'),(3,'FR'); /*!40000 ALTER TABLE `tbl_language` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_project` -- DROP TABLE IF EXISTS `tbl_project`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_project` ( `proj_id` int(20) unsigned NOT NULL auto_increment, `proj_title` varchar(255) default NULL, `FK_cust_id` int(20) unsigned default NULL, `FK_langsid` int(20) unsigned default NULL, `proj_start_date` timestamp NULL default NULL, `proj_end_date` timestamp NULL default NULL, `proj_status` enum('Open','Hold','Enquiry','Redundant','Complete') default NULL, PRIMARY KEY (`proj_id`), KEY `FK_cust_id` (`FK_cust_id`), KEY `FK_langsid` (`FK_langsid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_project` -- LOCK TABLES `tbl_project` WRITE; /*!40000 ALTER TABLE `tbl_project` DISABLE KEYS */; INSERT INTO `tbl_project` VALUES (1,'Project A', 1,2,'2012-01-30 09:00:00','2012-01-30 17:30:00','Complete'); /*!40000 ALTER TABLE `tbl_project` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_task` -- DROP TABLE IF EXISTS `tbl_task`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_task` ( `task_id` int(20) unsigned NOT NULL auto_increment, `task_title` varchar(50) default NULL, PRIMARY KEY (`task_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_task` -- LOCK TABLES `tbl_task` WRITE; /*!40000 ALTER TABLE `tbl_task` DISABLE KEYS */; INSERT INTO `tbl_task` VALUES (1,'Preparation'),(2,'Typesetting'),(3,'Correction'),(4,'Evaluate'); /*!40000 ALTER TABLE `tbl_task` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `tbl_task_item` -- DROP TABLE IF EXISTS `tbl_task_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tbl_task_item` ( `task_item_id` int(20) unsigned NOT NULL auto_increment, `FK_job_id` int(20) unsigned default NULL, `FK_task_id` int(20) unsigned default NULL, `status` enum('Pending','Ready','Sent','Active','Complete') default 'Pending', `task_complete_date` timestamp NULL default NULL, `task_order` int(20) unsigned default NULL, PRIMARY KEY (`task_item_id`), KEY `FK_task_id` (`FK_task_id`), KEY `FK_job_id` (`FK_job_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `tbl_task_item` -- LOCK TABLES `tbl_task_item` WRITE; /*!40000 ALTER TABLE `tbl_task_item` DISABLE KEYS */; INSERT INTO `tbl_task_item` VALUES (1,1,1,'Complete','2012-01-30 10:00:00',1),(2,1,2,'Complete','2012-01-30 11:00:00',2),(3,1,3,'Complete','2012-01-30 12:00:00',3),(4,1,4,'Complete','2012-01-30 17:30:00',4),(5,2,1,'Complete','2012-01-30 10:00:00',1),(6,2,3,'Complete','2012-01-30 12:00:00',3),(7,2,4,'Complete','2012-01-30 17:30:00',4); /*!40000 ALTER TABLE `tbl_task_item` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2012-01-30 18:59:47 QUERY: Code: [Select] SELECT DATE_FORMAT(proj_end_date, '%m') as month_number, MONTHNAME(proj_end_date) AS the_month, YEAR(proj_end_date) AS the_year, SUM(analysis_net) as analysis_net, SUM(analysis_gross) as analysis_gross, SUM(job_pages) as job_pages, SUM(invoice_item_price) as total, CASE WHEN task_title='Typesetting' AND ti1.status='Complete' THEN 'Typeset' ELSE 'Non-Typeset' END AS 'JobType', langname FROM tbl_job as j INNER JOIN tbl_task_item as ti1 ON ti1.FK_job_id = j.job_id INNER JOIN tbl_task as t ON t.task_id = ti1.FK_task_id INNER JOIN tbl_invoice_item as ii ON ii.FK_job_id = j.job_id INNER JOIN tbl_invoice as i ON i.invoice_id=ii.FK_invoice_id INNER JOIN tbl_analysis as a ON a.FK_job_id = j.job_id INNER JOIN tbl_language as l ON l.lang_id = j.FK_langt_id INNER JOIN tbl_project as p ON p.proj_id = j.FK_proj_id WHERE p.FK_cust_id = 1 AND proj_status='Complete' GROUP BY the_year, month_number, JobType, langname ORDER BY the_year DESC, month_number DESC, JobType DESC, langname ASC The second problem I'm having is in presenting the results in the correct way. From my example below, you can see what I'm trying to achieve. The 'commented-out' PHP code, should 'GROUP' the JobType results, just like how the 'the_month' is working. But again, becoming a bit stuck in getting this to work too. <table border="3" cellpadding="3" cellspacing="3"> <tr> <td>Month</td> <td>Year</td> <td>JobType</td> <td>Analysis Net</td> <td>Analysis Gross</td> <td>Pages</td> <td>Invoice Total</td> <td>Language</td> </tr> <?php $previousMonth = ''; $previousType = ''; do { if ($previousMonth != $row_rsProjType['the_month']) { //if ($previousType != $row_rsProjType['JobType']) {?> <tr> <td><?php echo $row_rsProjType['the_month']; ?></td> <td><?php echo $row_rsProjType['the_year']; ?></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <?php $previousMonth = $row_rsProjType['the_month'];} ?> <tr> <td></td> <td></td> <td><?php echo $row_rsProjType['JobType']; ?></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <?php //$previousType = $row_rsProjType['JobType'];} ?> <tr> <td></td> <td></td> <td></td> <td><?php echo $row_rsProjType['analysis_net']; ?></td> <td><?php echo $row_rsProjType['analysis_gross']; ?></td> <td><?php echo $row_rsProjType['job_pages']; ?></td> <td><?php echo $row_rsProjType['total']; ?></td> <td><?php echo $row_rsProjType['langname']; ?></td> </tr> <?php } while ($row_rsProjType = mysql_fetch_assoc($rsProjType)); ?> </table> If anyone can help me out in getting this to display correctly, it'll be highly appreciated. hello every one
I have some queries in mysql command..
I have a table schema like this
id | name | url | img1 | img2 | img3 | img4|
I want to randomly rotate the image by specific id....
suppose the select the id = 1, then the four images will be rotated randomly for with the tha id (i.e id = 1)
Any help wil greatly appreciated
Thank u.....
|
|||||||