|
PHP - Relational Mysql
Hi Guys
I have two table in mysql one is users and the second one is profiles my database name is movies users contains: users_id, username, password, email movies contains: id, user_id, name, movie_name, movielink I need users to login and then update their profile according to relational database setup so once user loggs in they can add the link of their movies to the database. Could some one help me with inserting data into relational database according to database setup I have introduced above? Appreciate your help in advance Similar TutorialsI have a relational table call sales with prodcut_id and custmer_id tables. I also have a product and customer tables. products table with product_id as an auto increment and customer with custumer_id I want to join through the sales tables all t he fields of row 1 from tables customer and products and pull it at once. This is a member login script and I want to display the products by members. So far I have this query to display the products once the member is login in. $userid is the id of the customer coming from the $userid= $_SESSION['customer_id']; $mysqlSales="SELECT products.* FROM products JOIN sales ON (products.product_id = procuct_id ) WHERE sales.customer_id = '$userid'"; so far that statement is not working where should I have some type of incoherance with the english statement above expressing what I want the query to do. I am creating a simple social network, and i want the post visible only on its circle of friends but the problem is... let say user_a, user_b, user_c already registered and user_a and user_c connected/friends already and all their posts and comments are visible on their circle but when user_b write a post oh his wall, it's also visible to user_a and user_c which i dont want to happen. I dont know what was wrong on codes below. CREATE TABLE IF NOT EXISTS `user`( `uid` INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL, `uname` VARCHAR(25) NOT NULL, `pword` CHAR(60) NOT NULL, `fullname` VARCHAR(30) NOT NULL, INDEX(`uname`) ) Engine = InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci; CREATE TABLE IF NOT EXISTS `friend`( `fid` INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL, `friend_id` INT(11) NOT NULL, `my_id` INT(11) NOT NULL, `stat` ENUM('0','1') NOT NULL, INDEX(`friend_id`, `my_id`), FOREIGN KEY(`friend_id`) REFERENCES `user`(`uid`) ON UPDATE CASCADE ON DELETE CASCADE ) Engine = InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci; public function viewFriendIfExistOnTbl($uid) { $query = $this->mysqli->query("SELECT `friend_id` FROM `friend` WHERE `my_id` = '$uid' LIMIT 1"); if ($query->num_rows > 0) { return true; } } I have a form where I have inserted 7 pre-populated relational lists. All of the information is pulling correctly from the databases, but when it posts, it's posting the value "ids" instead of the chosen text. The files a www.kcwell.com/gcc_form.php and www.kcwell.com/gccsuccess_form.php How do I set up a query to obtain the data that I need? Help! I am just new to relational algebra probably a pre-step before learning SQL queries. Can you help me make the expressions of relational algebra expression for each of the following queries. This is the table contained inside a bus driver database.
driver ( driver_id, driver_name, age, rating ); bus ( bus_id, bus_name, color); reserves ( driver_id, bus_id, date);a. Find the names of drivers who have reserved at least three busses. b. Find the names of drivers who have reserved all busses. c. Find the names of drivers who have reserved all busses called Shuttle. d. Find the IDs of drivers whose rating is better than some driver called Paul. I would be grateful if somebody can help me here.
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! 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.....
I'm having trouble working out a mysql query. I have two tables called "content" and "providers". Content Table content_id content_provider 1 Provider 1 2 Provider 2 3 Provider 1 Providers Table provider_name provider_url Provider 1 www.provider1.com Provider 2 www.provider2.com What I want to do is to select all of the information in the "providers" table but also to perform a count on the "content" table to get the number of content for each provider. So in this case it would show all of the information for provider 1 and also show that provider 1 has 2 pieces of content because they have 2 entries in the "content" table. I am able to get these results as two separate queries but I was wondering if it was possible to do it in one query. Thanks for any help. I am having a problem with my PHP/MySQL coding and I can not figure out what I am doing wrong. I have two tables in a database. One called EmployeeInfo and another called Absence. In the Employee Info table, I have two employees, Derek and Adrian. In the Absence table, I have two records, One for Derek and Adrian. Goal: Using PHP/MySQL, I want to call upon an employee using a form and display that person's absence. The form pulls up correctly. If I do NOT put a WHERE clause into my SQL statement, it pulls up all the absences perfectly fine. Once I put in the WHERE = EmployeeInfo.Name = 'NameSelect' then it fails every time. Here is my SQL Coding: $result = mysql_query("SELECT * FROM Absence LEFT JOIN EmployeeInfo ON(Absence.Account = EmployeeInfo.Account) WHERE EmployeeInfo.Name='NameSelect'"); Any help is greatly appreciated. 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. This is my first time really working with more than 1 or two tables in a mysql database and I'd like to know what is the best way to go about querying my data so that i'm doing it in at least a somewhat efficient manner that the way I am currently doing it. First I will list my Tables and pastebin the Table creation code so you can get an idea of what I'm working with. Graff_Users, Drawings, Drawings_Stamps, Stamps, Packages Ok, those are the tables, the Graff_Users is the main table. The way I've got things setup is that I first do a query on the Graff_Users table for any uid that I want to lookup data for. Then, based on whether the artist column is equal to 1 will determine if there are Drawings to fetch, at least that is how I've been doing it and I dont feel like redesigning something that works so well for me right now. so everything is fine when artist=0 or null or anything but 1. But my trouble starts if artist=1, then I have to query a whole lot of data here, let me explain it. Ok, if artist=1, currently, I query the Drawings table to find any drawings where the house_owner_id is equal to the uid of the User. This leads me to a list of Drawings rows. Next, I check which of those rows has stamps_used=1 for each Drawing and gather up a list of id's of the Drawings that have that set to 1. Once I have the list of id's of the Drawings, I query the Drawings_Stamps table for rows where the drawings_id is equal to any of those id's. Then once I have that, I use the stamp_id's from those and do another query on the Stamps table to get all of the stamps matching those id's. Then each Stamp also contains an author_id so I gather those id's up and do another query back on the Graff_Users table to get the first_name,last_name and picture. Once I have all of that data in various array throughout my script, i create a new array and start putting the pieces back togather from the various arrays until I am left with an array that looks like this one here(JSON used for visualization here) Code: [Select] { "request_type": 1, "target_uid": "1234567", "data": { "id": "277", "uid": "1234567", "first_name": "Jeremy", "last_name": "Canada", "picture": "http://www.pictures.com/mypic.png", "signed_up": "2010-10-29 03:47:09", "selected_house": "10", "mp3": "1", "emblem_id": "2", "artistData": [ { "id": "210", "artist_id": "1234567", "house_owner_id": "1234567", "anonymous": null, "stamps_used": "1", "timestamp": "2010-09-23 00:52:42", "note": "wanna dance?", "first_name": "Jeremy", "last_name": "Canada", "picture": "http://blahblah.com/picture.png", "stampsArray": [ { "id": "250", "name": "", "author_id": "654321", "created": "2010-09-23 23:40:22", "keywords_id": null, "packages_id": "27", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285221_445643.png", "number_of_users": "0", "description": "just another stamp my friend", "rating": "0", "thumbs_up": "0", "thumbs_down": "0", "first_name": "Cat Man", "last_name": "Blues", "picture": "http:\/\/pic.com\/_q.jpg", "packageData": { "id": "27", "name": "Circles Of cool", "description": null, "total_stamps": "6", "number_of_users": "0", "rating": "0", "thumbs_up": "0", "thumbs_down": "0" } }, { "id": "249", "name": "", "author_id": "1680999989", "created": "2010-09-23 23:38:28", "keywords_id": null, "packages_id": "26", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285108_441450.png", "number_of_users": "0", "description": null, "rating": "0", "thumbs_up": "0", "thumbs_down": "0", "first_name": "Raymond", "last_name": "Coldstar", "picture": null, "packageData": { "id": "26", "name": "The nice pics pack", "description": "description of the package would go here", "total_stamps": "123", "number_of_users": "234", "rating": "3", "thumbs_up": "2", "thumbs_down": "7" } } ] }, { "id": "212", "artist_id": "4", "house_owner_id": "1234567", "anonymous": "0", "stamps_used": "1", "timestamp": "2010-07-09 03:45:59", "note": "shake it baby", "stampsArray": [ { "id": "248", "name": "flying stick man 1", "author_id": "663231980", "created": "2010-09-23 23:37:08", "keywords_id": "6", "packages_id": "26", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285027_288422.png", "number_of_users": "1200", "description": "stamp description would go here", "rating": "6734", "thumbs_up": "18", "thumbs_down": "22", "first_name": "Gary", "last_name": "Hoss", "picture": "http:\/\/profiles.com\/63231980_1168_q.jpg", "packageData": { "id": "26", "name": "The nice pics pack", "description": "description of the package would go here", "total_stamps": "6546345", "number_of_users": "234", "rating": "3", "thumbs_up": "2", "thumbs_down": "7" } }, { "id": "249", "name": "", "author_id": "1680999989", "created": "2010-09-23 23:38:28", "keywords_id": null, "packages_id": "26", "public": "1", "package_category_id": "0", "filename": "drawingitems\/663231980_1285285108_441450.png", "number_of_users": "0", "description": null, "rating": "0", "thumbs_up": "0", "thumbs_down": "0", "first_name": "Raymond", "last_name": "Blues Brutha", "picture": null, "packageData": { "id": "26", "name": "The nice pics pack", "description": "description of the package would go here", "total_stamps": "6543534", "number_of_users": "234", "rating": "3", "thumbs_up": "2", "thumbs_down": "7" } } ] }, { "id": "221", "artist_id": "663231980", "house_owner_id": "1234567", "anonymous": null, "stamps_used": null, "timestamp": "2010-09-13 07:35:25", "note": "hi jeremy", "first_name": "Coolio", "last_name": "Stars", "picture": "http:\/\/nowhere.com\/1980_1168_q.jpg" }, { "id": "227", "artist_id": "4321", "house_owner_id": "1234567", "anonymous": null, "stamps_used": null, "timestamp": "2010-09-24 01:24:19", "note": "" } ] } } Hey Guys I need some help, I have these two tables: (vouchers) (login) I also, have this page. (redeem.php) What i need, is when a user types in a voucher code, i adds 100 credits to there user account. My Logged in Session is currently: Quote $username = $_SESSION['loggedinsuccesfully']; Also, when the voucher has been used, the status of the voucher needs to change to 1 (means it cant be used again) If anyone can offer me this code, i will appreciate it. Thanks again fCooper94 I have a CVS file with about 1000 entries which I need to input into a MySQL database. Each line in the file has 5 fields but I am only interested in the first two. These are the description and name. I'm sure I can do this directly with MySQL but I need to use PHP anyway because there is actually other things I need to do with the data. I don't need to go into details because I already have that sorted. I just need to know how to open the CVS file and make a mysql insert loop with the data. Thanks Hello i want to create a comment function to my website, but im not sure which way to do it. The thing is that i have post.php script that insert a new row in my table "content" and i want 1 separate comment function for each row (that symbols a link on my website). I was thinking that i could add a new table each time a new row(link) is created on my website just by modifying post.php to create new table. But will this be the most officiant way of making a comment function or will it burden my server and slow'en it? I am creating a website for university coursework.
<input name="submit" type="submit" class="submit_btn float_l" id="submit" formaction="add-contactme.php" value="Send" />
The above code is from my html for contact.html if that is correct. I am using a form and then a submit button.
I keep getting
Notice: Undefined index: name in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 11 Notice: Undefined index: email in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 12 Notice: Undefined index: phoneno in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 13 Notice: Undefined index: comments in C:\xampp\htdocs\hometown\hometown\add-contactme.php on line 14 Also if anyone could help me I would like to create a delete button after a person has added the values into the sql table, basically to the last row inputted into the database. I want to avoid using delete from contactme where name ='chris';
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="contactme"; // Table name
mysql_connect ("$host", "$username", "$password") or die ("cannot connect server ");
mysql_select_db ("$db_name") or die ("cannot select DB");
$name = $_POST["name"];
$email = $_POST["email"];
$phoneno = $_POST["phoneno"];
$comments = $_POST["comments"];
$sql="INSERT INTO $tbl_name VALUES ('$name', '$email', '$phoneno''$comments')";
$result=mysql_query($sql);
if($result){
echo "Successful" . "
";
echo "view-contactme.php"; // link to view contact me page
}
mysql_close();
?>
Edited by G3NERALCHRIS, 20 November 2014 - 09:04 AM. Hello everyone, need some help here.. Below are my codes.. ----------- $date = array("1001", "1002", "1003"); $sql="select ID from fid;"; $result = mysql_query($sql) or die("Query failed : " . mysql_error()); while($row = mysql_fetch_array($result, MYSQL_NUM)) { $test_id = $row[0]; foreach ($date as $datex){ $sql2="UPDATE tbl1 SET date1=(SELECT date1n FROM dat".$datex." WHERE ID=".$test_id.")"; mysql_query($sql2); ----------- I have tables 'dat1001','dat1002' and 'dat1003'. I want to get some values from that table and store them in another table ' tbl1'. It is updating but the values are zero. Thanks in advance... |
|||||||