Search the Community

I am wondering if there is a way to use this book without performing the complex joins that have UNION statements in them. I think it is chapter 9 or 10 of the book, the chapter that shows the most complex mysql statements. How could I go about using this book without using those complex mysql joins.

Hello! I'm doing the appendix test PHP and SQL page and i keep getting a 'object not found' page. The requested URL was not found on this server. Can anyone fill in what i maybe doing wrong here. I checked the spelling it's not that.

I want to get the primary key values of movie and actor table. When i selected values in radio button primary key has been created automatically to the movie and actor table. Now I want to get that primary key value and insert to the movie_actor table. my code connect.php <?php $dbc = mysqli_connect('localhost','root','black98765','db_name') OR die("Cannot connect to MySQL:" . mysqli_connect_error()); //insert into actor table $q = "INSERT INTO actor (name) VALUES ('$actor')"; //insert into movie table $q2 = "INSERT INTO movie (movie_name, release_year) VALUES ('$movie','$year')"; //movie_actor table $q3 = "INSERT INTO movie_actor (movie_no,actor_no,rate) VALUES ('$rate')"; //what value i need to put inside values for movie_no and actor_no? //connect and insert $q $r = mysqli_query($dbc,$q); $r2 = mysqli_query($dbc,$q2); $r3 = mysqli_query($dbc,$q3); if($r && $r2 && $r3){ echo "Inserted Successfully!"; }else{ echo "Failed to Insert Data!"; mysqli_error($dbc); } mysqli_close($dbc); ?> form.php <?php if(isset($_POST['submit'])){ if($_SERVER['REQUEST_METHOD'] == 'POST'){ $error = array(); //choose actor if(!isset($_POST['actor'])){ $error[] = "Please choose of the following actors!"; }else{ $actor = $_POST['actor']; } //choose movie if(!isset($_POST['movie'])){ $error[] = "Please choose of the following movies!"; }else { $movie = $_POST['movie']; } //choose release year if(!isset($_POST['year'])){ $error[] = "Please choose of the following release year!!"; }else{ $year = $_POST['year']; } //choose rate if(!isset($_POST['rate'])){ $error[] = "Please choose of the following rate!"; }else{ $rate = $_POST['rate']; } //if no errors if(empty($error)){ require('connect.php'); }else{ echo "<p>System Error!</p>"; foreach($error as $msg){ echo $msg."<br/>\n"; } } } } ?> <form action="form.php" method="POST"> <p>Select on the following Selections</p> <p><label for="actor">Name of Actor:</label> <input type="radio" name="actor1" value="Jet Li"/>Jet Li <input type="radio" name="actor2" value="Sylvester Stallone"/>Sylvester Stallone <input type="radio" name="actor3" value="Jason Statham"/>Jason Statham</p> <p><label for="movie">Name of Movie:</label> <input type="radio" name="movie1" value="Expendables 3"/>Expendables 3 <input type="radio" name="movie2" value="Rocky"/>Rocky <input type="radio" name="movie3" value="Kiss of the Dragon"/>Kiss of the Dragon</p> <p><label for="movie">Release Year:</label> <input type="radio" name="year1" value="2014"/>2014 <input type="radio" name="year2" value="1976"/>1976 <input type="radio" name="year3" value="2001"/>2001</p> <p><input type="submit" name="submit" value="Insert"/></p> </form>

in the book it shows us how to drop an index by using the following sql statement. ALTER TABLE t DROP INDEX i i being the name of the index. I went into phpmyadmin and I don't see an option for removing an index unless there is a name attached to the index. If I would write an sql statement to drop the index how would I write it if the index doesn't have a name.

Ok, so I imported the SQL code for the second site. Now im in the designer view so I can physically see everything. When I start to create the foreign key links it goes well until i try to link "non_coffee_products" to "sales". I successfully linked "specific_coffees" to "sales" and after I did I still could not link "non_coffee_products" to "sales", but this time I get Error: Relation already exists. So my questions are 1. Why wont the foreign key work between "non-coffee-products" and "sales"? 2. How do I link them both to "sales" if only one relation can be set up? Thank you for looking into my inquiry.

I have an images table that has 5 foreign keys that reference 5 other tables, each of those tables uses the images table to store images, when an image gets uploaded, the foreign key in question gets a value associated with it in the images table but the other 4 foreign keys will get NULL values in that row. Is that acceptable, or should I rethink my database design? What would be the best way to go?

The select query below returns 1 row when it should be 3. I am pretty sure it is because of the AVG(k.sumtotal) field. If I rewrite the query and take out that AVG(k.sumtotal) column and take out the FROM inv_ratings AS k, I get my 3 rows. I looked online for hours trying to find information about returning results using the AVG clause and didn't find much. Do I have to use a group by clause, I tried that and only get errors. If it is a group by clause please type the exact group by clause to use if you could. thank you. $p = "SELECT i.invention_id, i.inv_title, i.date_submitted, i.category_id, i.approved, c.category_id, c.category, u.image_name, AVG(k.sumtotal) FROM inv_ratings AS k INNER JOIN inventions AS i USING (invention_id) INNER JOIN categories AS c USING (category_id) INNER JOIN images AS u USING (invention_id) WHERE c.category_id = $cat AND i.approved = 'approved' HAVING u.image_name < 2 ORDER BY date_submitted DESC LIMIT $start, $display"; $q = mysqli_query($dbc, $p) or trigger_error("Query: $p\n<br />mysqli Error: " . mysqli_error($dbc));

I've got your first edition of Effortless E-Commerce with PHP and MySQL. I've downloaded the script files and installed into xampp. The first issue I've identified is that some stored procedures are missing. So far, get_shopping_cart_contents and get_wish_list_contents. Looking in my phpMyAdmin, I can see 3 routines. None of them the same as the procedures I'm looking for. Are these routines the same as stored procedures? Looking in the book I find code for get_shopping_cart_contents and 3 other procedures for the cart, so I presume I can create them in the database. However the procedure get_wish_list_contents can't be found in the book or errata. How do I get that info? How much more information am I missing?

Hi. I'm modifying the 'quotes' script in chapter 13 to include a statement before the quotes that indicates a 'new' or 'recent' quote if the quote was entered within the last 30 days. I know I need to use $row[date_entered] (type timestamp in mysql, e.g. 2014-03-09 13:25:11). Is there a function to compare that timestamp with today (in number of days)? Thanks.

Hello everyone, I am have a problem that I am not able to fix. As explained in the book I don't see any values for my dates and times for the message board. I have downloaded the file (http://dev.mysql.com/downloads/timezones.html) to update my mysql installation but with no success. My question is how can I update my mysql installation on a wamp environment? Your help will be greatly appreciated. Regards, Gralf

In Chapter 19, the $_SESSION['customer_id'] variable is often used for things like isset($_SESSION['customer_id']) why though can't we use other table columns that are like customer_id in the $_SESSION[] for example $_SESSION['order_id'] In chapter 19 also, the $_GET['id'] is the same id as the customer's. Why is it the same? Where does it get declared as the same, I have searched through the scripts and I don't see it. I am trying to create a script where users can view their past orders. The trouble I am having is calculating the total amount of the order in the checkout.php script, because there is no customer_id field in the order_contents table. Here is where I am at with that in the checkout.php script: $u = "SELECT price * quantity AS amount FROM order_contents WHERE order_id=?not sure what to put here"; $total = mysqli_query($dbc, $u);

hi im a java/ newbie php developer. I recently bought your book and it has great content, but I an failed to open stream error. I have removed the path/to/dir with the correct value which is '/C:/xampp/htdocs/' and im still getting an error could there be something wrong with my path variable??

My question is why in the following mysql statement does the $id at the end, not have to be in single quotes, whereas other variables in this statement are in quotes. $u = "UPDATE customers SET first_name='$fn', last_name='$ln', email='$em', address='$add', zipcode='$zip', city='$city', state='$state', country='$coun' WHERE customer_id = $id";

I am using the structure of Script 3.5 (Calculator.php) to tackle the issue of recording attendance for persons listed in a MySQL database. I am using this structure because I am attempting to create a web page that will handle a form and then display it again. Larry mentions this function on page 86. I plan to use input type="checkbox" name="checkbox[]" value="full_name". The value part is where I am having trouble. I want to select a person's name from a table in the database so that the name will appear on the form on the same line with the checkbox. The page is in two parts, the first part being for php code, and the second part being for the html form. To place on the form a checkbox I need to list a person's name with that input. I can query the database in the php section and fetch from the array of results each person's name. Can anyone suggest where in the code I can fetch the names and then use them in the input for checkboxes? I hope this makes sense. I want to record attendance for all persons in a table who are present at a meeting. I want to list the names of all persons in the table along with a checkbox that can be checked to indicate they were present. I will appreciate any guidance that can be shared to help me in designing a plan to accomplish this. Thank you. Wes (wesmith4@gmail.com)

I have an HTML table generated by PHP querying from MySQL table. <table> <tr> <th>Sl</th> <th>ID</th> <th>Article Name</th> <th>Publish Status</th> </tr> <?php $i = 1; foreach ($obj->showAllFromTable('pattern') as $pattern) { extract($pattern); ?> <tr> <td><?php echo $i++; ?></td> <td><?php echo $id; ?></td> <td><?php echo $pattern_name; ?></td> <td id="publish_<?php echo $id; ?>" class="status_pattern"> <?php echo $publish; ?> </td> </tr> <?php } ?> </table> I want to change the status of any article by clicking on the 'publish' cell of the corresponding article row. I am trying to use ajax method of jquery for this purpose as shown in the following: <script type="text/javascript"> $(document).ready(function(){ $('.status_pattern').click(function(){ var thisid = $(this).attr('id'); $.ajax({ url: "status_change_pattern.php", data: { id: thisid }, success: function (response) { alert(response); } }); }); }); </script> In the "success" block, instead of "alert", I want to create a functionality to change the text of the specific cell which I clicked. The "status_change_pattern.php" has just a text "Hey Hey". Can anyone help? Please. Thanks.

hey everybody, i have a request here,i'm a newbie in which called php and mysql.. i have finished the HTML of my website and now i want to implement a sidebar for Most Popular Posts.. i'masking if u can afford me with a tutorial from the scratch to build my sidebar? plz? thanks in advance

I am going through chapter 9, "CMS with OOP", and I have encountered an error when I finally visited the site (after building a few files first). I reviewed my syntax thoroughly, then downloaded the files from this site just to see if my code was wrong, but the home page (index.php) still displays the following error: SQLSTATE[28000] [1045] Access denied for user 'username'@'localhost' (using password: YES)[/size] The "pages" table in the "cms" database does indeed have rows inside it, I am using the correct username and password for my database (checked in the mysql command line), and I ran a php_info( ) to verify that PDO is enabled. Can anyone offer a suggestion as to the issue? I reviewed the forums but saw no question pertaining to this; if this is a duplicate, I apologize. EDIT: My search engine of choice is telling me it's some sort of permissions issue, but I haven't found any fixes. Still looking. Now that that's the issue, I'm not sure if this question is still within the scope that the community can answer it since this is a PHP and not a MySQL book. If so, help would be much appreciated!

Hi, I need some help with a php application. I want to write simple online scheduling/appointment application with PHP and MySQL. I am not sure how to begin. Basically these are what the application would allow: USERS : - to log in as member - choose the person (advisors) they want to make an appointment with - pick a date and time from available time slot For example, Feb 1, 2014, 8:00AM- 8:30AM - Submit the date to advisor (email confirmation) ADVISORS - to log in as an admin - set up their schedules (available date and time). - there are several advisors, each has seperate schedule. My questions are: - What tools/frameworks should be used to display Calendar and time? - What appropriate methods should I store advisors available date and time in MySQL? - If i use jquery UI to display calendar, how can I make it interact with the database? For example, GREEN is available time, RED is booked. Thanks for your input.

Hi, Larry In page 294. I try to break down the add_order procedure into separated piece since my shared host doesn't support routine. The problem lies in the last two statement. I believe the subtotal is a temporary column to store the result of quantity * price_per and then deal with the UPDATE statement that comes after. So, what's the best way to rewrite those two statements as normal sql query? I personally try to combine those two queries since they should work as one: $query = "UPDATE orders SET total=((SELECT SUM(quantity*price_per) INTO subtotal FROM order_contents WHERE order_id=$oid) + $shipping ) WHERE id=$oid"; Do you think this gonna work?

Hello Larry, Thanks for another great book. When I try to create users table for pdf store, I get an error: 'ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause'. I tried couple times get the exactly same error. Is that mysql doesn't allow creation of two TIMESTAMP in two columns (date_created & date_modified) ?

Last week I purchased Mr. Ullman's "PHP and MySQL for Dynamic Web Sites" 4 edition. I have never tried working with php, nor mysql before. From my home, I have a stand-alone computer acting as a server. I am running Microsoft 2008 server on this computer. I also have Apache 2.2 running with no problems. I have installed "MySQL 5.6, and PHP 5.3.27, and again, no problems. I have a static ip address, and am hosting a small experimental website. I made the site with Dreamweaver. Wanting to try some script, I skipped ahead to Chapter 5, and experimented with some of the script. Using the "MYSQL Command line" lessons, I created a Database named "Bfdata." I then created a Table named "Members," with only two columns. The first column is named "username," and the second column is named "password." I then inserted 3 different users with the name of 1, 2, and 3 (those are the actual names I created for experimental sake) I gave each a password of xyzzyx123321 (that's the actual password for each one I created, also for experimental sake) I then went ahead to Chapter 9, and tried out the script 9.2 called "mysqli_connect.php" Try as I may, I couldn't get the file to communicate with the "mysql server." According to Mr. Ullman's tips, my php version might not support the "mysqli" function. So I searched the web and found a configuration that works good. The script reads as follows: <?php $link = mysql_connect('localhost' ,'john' , 'xyzzyx'); if (!$link) { die('could not connect: ' . mysql_error()); } echo 'connected successfully'; ?> My question is this: Obviously with the above script, I'm connecting to the Mysql server, but what I want the script to do, is not only successfully connect, but to then access the table called "members," and check for the "username" called 1 and check 1's password which is "xyzzyx123321," or "username 2, or 3 which also have the same password. Once the user name and password are successful , I'd like the script to pass the successful user on to a html page called upload that I made. Would someone write that script and either post it, or send it to my johninbellingham@hotmail.com email address. Thank you much, johnjwe

Hello, I have been having trouble with this for quite some time I have tired to narrow it down the best I can. I able to complete the registration process, I went and check the sql database all fields have been filled. I think that the activation page cannot access the database to remove the active code. I tried to print or echo out the query value with echo "<p>Q: $q</p>" or print "<p>Q: $q</p>" all I see on the screen is "Q:" When I click the link from my email it doesn't seem to fail as it does not redirect me to index.php. I also do not get any error on my screen either. Any would help would be greatly appreciated. Regards Alex <div id="activate"> <?php /** * @author Alex Power * @copyright 2013 */ # Script 18.7 - activate.php require ('includes/config.inc.php'); $page_title='Activate Your Account'; if (isset($_GET['x'], $_GET['y']) && filter_var($trimmed['email'],FILTER_VALIDATE_EMAIL) && (STRlen($_GET['y']) == 32 )) { require (MYSQL); $q = "UPDATE users SET active=NULL WHERE (email='" . mysqli_real_escape_string($dbc, $_GET['x']) . "' AND active='" .mysqli_real_escape_sting($dbc, $_GET['y']) . "') LIMIT 1"; print "<p> Q: $q</p>"; $r = mysqli_query($dbc, $q) or trigger_error("Query:$q\n<br />)MySQL Error:" . mysqli_error($dbc)); if (mysqli_affected_rows($dbc)== 1 ) { echo "<h3>Your account is now active. You may now login.</h3>"; } else { echo '<p class="error">Your account could not be activated. Please re-check the link or contact the system administrator.</p>'; } mysqli_close($dbc); } else { //Redirect $url = BASE_URL . 'index.php'; ob_end_clean(); header("location:$url"); exit(); } // END of main IF-ELSE ?> </div>

Hello, I tried to insert multiple image into image table using a foreach loop. The images insertion have returned no problem. But I wanted to catch the image_id of each insert and putted them into an array ($iid). Here is problem, no matter what I tried, the result always return 2 separated array: Array([0]=>9193) Array([1]=>9194). Is there anyway to solve this problem? I want Array([0]=>9193 [1]=>9194). Thank you all!. CODE: // query for media $query = 'INSERT INTO media (file_title, file_mimetype, file_type, file_url, file_url_thumb, file_is_product_image, created_on) VALUES (?,?,?,?,?,?,NOW())'; $stmt = mysqli_prepare($dbc, $query); $i = 0; foreach ($image_name as $key => $value){ $dot = strpos($value, '.'); $file_title = substr($value, 0, $dot); $extension = str_replace('.','',substr($value, $dot)); $file_mime = 'image/'.$extension; $file_type = 'product'; $file_url = $value; $file_url_thumb = 'images/product/resized/'.$file_title. $upload->getSuffix().$extension; $fipi = 1; mysqli_stmt_bind_param($stmt, 'sssssi', $file_title, $file_mime, $file_type, $file_url, $file_url_thumb, $fipi); mysqli_stmt_execute($stmt); // check result: if(mysqli_stmt_affected_rows($stmt) == 1){ // get the image id $iid = array(); $iid[$i++] = mysqli_stmt_insert_id($stmt); print_r($iid); // RESULT: Array([0]=>9193)Array([0]=>9194) } else { echo 'Insertion failed' . mysqli_stmt_error($stmt); } } // End of foreach

Hi, I get a strange problem when I try to insert images data into media table. I decided to break down the problems section by sections. I rewrite the query using basic mysqli_query() function and insert one row at a time for testing purpose. Here is the strange thing, when I pass the value as variable into the VALUES, the insertion will fail and return an error: 'insertion failedUnknown column 'edvard' in 'field list''. However, If I just pass the string into VALUES(), the insertion goes without problem. I haven't run into this sort of problem before, can anyone explain the reason please. Thanks again! Failed Code: $file_title = 'edvard-much-the-scream'; $file_mime = '.jpg'; $product_type = 'product'; $file_url = 'edvard-much-the-scream.jpg'; $fipi = 1; $query = "INSERT INTO media (file_title) VALUES ($file_title)"; // this fails mysqli_query($dbc, $query); if(mysqli_affected_rows($dbc)==1){ $iid = mysqli_insert_id($dbc); echo $iid; } else { echo 'insertion failed' . mysqli_error($dbc); } Success Code: $file_title = 'edvard-much-the-scream'; $file_mime = '.jpg'; $product_type = 'product'; $file_url = 'edvard-much-the-scream.jpg'; $fipi = 1; $query = "INSERT INTO media (file_url) VALUES ('edvard-much-the-scream')"; // this works mysqli_query($dbc, $query); if(mysqli_affected_rows($dbc)==1){ $iid = mysqli_insert_id($dbc); echo $iid; } else { echo 'insertion failed' . mysqli_error($dbc); }

Hello there, It's my first time asking question here, so I want to introduce myself a little bit. I was a graphic designer and 3D artist and I have never done any website except using iWeb. The company I work for needs to rebuild its website which has roughly four thousand products in the database. The website was built by hosting company using a open source CMS called OScommerce. My given project was to create a independent website using php and mysql that can be easily modified to suit the need when time is changing. When I receive the project around beginning of this year, I knew nothing about php and mysql so I went to amazon to buy 2 books. One was "PHP Solutions Dynamic Web Design Made Easy" and other one "Modern Javascript". I studied through both books. The PHP Solution is a good book, but not for absolute beginner. The way it organizes its content, mixing basic and advance concerts and methods together into one book keeps new learner confused and frustrated. So I wasn't really fully understand the material I have learned. As comparison, Modern Javascript is much clear to me and easy to learn. After finished Modern Javascript, I found Mr. Larry also wrote other php books, so I decided to pick up PHP and MySQL for Dynamic from Amazon. As I studied through (followed all examples), I found this book really helped me clear up the concert that I wasn't really clear of. So ends up, I purchased all php book from Larry and finished all of them. Now back to the question, I run into problem to insert timestamp into prepare statement, after some goolge search, there is answer in stackoverflowhttp://stackoverflow.com/questions/14096220/datetimes-now-function-does-not-work-with-pdo-mysql-prepared-statements I change my script according to it, but it returns an error: mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables. I have no idea now, can anyone show me the right way to this please. Thanks. CODE: // query for products table $query = 'INSERT INTO products (product_sku, product_name, product_size, product_color, product_packaging, product_desc, created_on, new_arrival, feature, publish) VALUES (?,?,?,?,?,?,NOW(),?,?,?)'; $stmt = mysqli_prepare($dbc, $query); mysqli_stmt_bind_param($stmt,'ssssissiii', $sku,$name,$size,$color, $packaging,$desc, $new_arrival,$feature,$publish ); mysqli_stmt_execute($stmt);