Jump to content
Forums are read-only ×
Larry Ullman's Book Forums

Antonio Conte

Members
 View Profile  See their activity

  • Posts

    1084

  • Joined

  • Last visited

  • Days Won

    126

 Content Type 

Posts posted by Antonio Conte

    Foreign Key Coffee Question

    in Effortless E-Commerce with PHP and MySQL

    Posted

    Almost all my SQL experience is in T-SQL so maybe I just don't understand MySQL syntax. I'm probably missing the obvious (as usual with me) and pre-apologize if I am.

    Thanks in advance for your help.

     

    Hey,

     

    I'm not sure how T-SQL works. In MySqli, we don't define foreign keys in the table structure - we do it in our queries.

     

    I don't know how the structure for table "non_coffee_products" is. The columns in the SELECT statement is just made up by me.

     

    SELECT product.id, product.name, category.name

FROM non_coffee_products AS product

INNER JOIN non_coffee_categories AS category ON (product.category_id = category.id)

     

    I don't know if T-SQL has Inner Joins, but I'm guessing they do. Hope it makes sense. Several FROM tables and a WHERE clause might also work if you don't need category names etc.

     

    • Upvote 1

    Inserting Formatted Date.

    in PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide (3rd Edition)

    Posted

    I like to use three different HTML inputs for dates. It just makes it easier to prevent date mistakes in my head. I also like to use jQuery to dispay DD / MM / YYYY in the field before click.

     

    Use the function checkdate. It will not accept anything but integers and correct dates. (30. february is giving faluire.) This function is relying on it.

     

    <?php

function dateCheck($day, $month, $year) {
if ( checkdate((int)$_POST[$month], (int)$_POST[$day], (int)$_POST[$year]) ) {
 	return $date = $_POST[$year].-$_POST[$month].-$_POST[$day];
} else {
 	// error handling
}
}
?>

     

    To work with numbers FROM the database, use something like substring.

     

    $day = substr("$row['dato']", 8, 2);
$month = substr("$row['dato']", 5, 2);
$year = substr("$row['dato']", 0, 4);

    Outputting Stdout From Exec Call

    in PHP 5 Advanced: Visual QuickPro Guide (2nd Edition)

    Posted 

    exec ("/Applications/MAMP/htdocs/bin/pdftotext -f 1 -l 1 " . $pdf_filename . ".pdf " . $pdf_filename . "1.txt");

     

    I haven't tried this, but try adding more parameters to the function.

     

    <?php

$command = "/Applications/MAMP/htdocs/bin/pdftotext -f 1 -l 1 ";

exec ($command . $pdf_filename . ".pdf " . $pdf_filename . "1.txt", $return_array, TRUE);

foreach ($return_array as $output) {
echo $output;
}

?>

    • Upvote 1

    Q About Multiple Inner Joins

    in Effortless E-Commerce with PHP and MySQL

    Posted

    I've seen that many struggles with more advanced SQL-queries. I've thought about writing a long post about queries, but where does one start?

     

    Mysqli is so full of functionality, but very little of it is normally used by PHP users. We like to manipulate data with PHP, often making things a lot harder for ourselves.

     

    Thank you for the nice words, Jonathon. I've written on UK and English board since I was 12-13 years old, and it's been great practice for me. :)

     

     

    Q About Multiple Inner Joins

    in Effortless E-Commerce with PHP and MySQL

    Posted

    My first question is, with inner joins, how important is the order? Does the order matter at all? If so, why? Sorry if that's too broad a question, but at the moment, I am having trouble imagining the whole situation.

     

    I'm not 100% sure of this. It matters in how the data is presented to you in SQL. It does not matter with PHP, as you specify which columns you would like to display, and how you would like to display them.

     

    FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id

     

    So this is going to find all instances where id in the sizes table is equal to size_id in the specific_coffees table, and then pull the appropriate data for those rows, right? Is my thinking correct here?

     

    Yes, you are absolutely right.

     

    Okay. So here's where it gets interesting. Is the second inner join acting off the results of the first inner join? In other words, the first inner join is performed, and then among those (filtered) results, we are running a second inner join that is further filtering the results.

     

    Inner joins does not filter data. It connects data. That is how you would want to look at it. Regarding your question: Think of the columns vertically. Another Inner Join will add columns to the right of the column that you are joining on. Coffee_id: 13 / Size: 0.5 (kilo) / Height: 17 (cm) / etc. Are you following?

     

    Or perhaps, are the two inner joins independent, and the results from both are being concatenated together? This is where my understanding is weak. I tend to believe that from a logical standpoint, the former is more likely, but I really don't know..

     

    And I did not read this before I answered your last quote. English is not my primary language, but this sounds more like the correct explanation of what happens. Arrest me if I'm wrong.

     

    In the end, I suppose I want to clarify whether the following two inner join statements would produce the same results or not:

     

    FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id
INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id

FROM specific_coffees AS sc INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id
INNER JOIN sizes AS s ON s.id=sc.size_id

    As stated earlier, I think this will produce the same result. I cannot absolutely guarantee it, but why not do a test in phpMyAdmin or something? :)

     

    And lastly, once the two joins have been performed, then the results are limited one more time by the WHERE clause, right? In essense, after the joins, we only want the results related to one type of coffee and for coffees in stock, right?

     

    Correctamento.

     

    As a random side note, the book uses the notation "ORDER by" at the end of the query, but I've always used "ORDER BY". I suppose it doesn't matter though.

     

    ORDER BY, order by, order BY, ORDER by... It does not matter. But you should really make it easier to write your queries. Larry has limited space, but you should make it easy to read.

     

    Look at something like this:

     

    SELECT coffee.id, coffee.name, coffee.price, type.bean_name, type.roast, package.height, package.width, package.weight

FROM spesific_coffees AS coffee

INNER JOIN sizes AS package ON (coffee.id = package.size_id)

INNER JOIN general_coffees AS type ON (coffee.id = type.general_coffee_id)

WHERE (package.size_id = 4) && (type.general_coffee_id = 2)

ORDER BY coffee.name, coffee.price

     

    Isn't this just WAAAY more simple to read? I've changed you names a lot here, but have tried to keep it logical.

     

    Hope you are get something from this. A second time: Write queries in something like phpMyAdmin to check your results!

    • Upvote 1

    Online Publishing - Selling Virtual Products

    in Effortless E-Commerce with PHP and MySQL

    Posted

    "appears saying the page has been accessed in error".

     

    The problem at least start in this code:

    // Add the message to the database:
$r = mysqli_query($dbc, "INSERT INTO page_posts (user_id, page_id,  message) VALUES ({$_SESSION['user_id']}, {$_GET['id']}, '$m')");

if (mysqli_num_rows($r) != 1) { // Problem!

	$page_title = 'Error!';
	include ('./includes/header.html');
	echo '<p class="error">This page has been accessed in error.</p>';
	include ('./includes/footer.html');
	exit();
}

     

    The query is obviously not what you wanted - thus mysqli_num_rows is not 1. (It's likely zero)

     

    Do some checks against PhpMyAdmin to check if the query is right when used manually. (Use values instead of get, session and variables.) Often, the errors can be small and hard to catch like "page_posts" should really be "page_post".

     

    Hope you figure it out.

    • Upvote 1

    Having Trouble Understaning The Sort Functions

    in PHP 5 Advanced: Visual QuickPro Guide (2nd Edition)

    Posted

    I had that too. It's actually very simple.

     

    // A normal variable containing a number
$a-number = 10;

// This is 10 because it's before we use the function
echo $a-number;

// Now we use our function (see bottom) on this variable
add_ten($a-number);

// This is now 20 because of the function below
echo $a-number;

// This is a function that adds ten to the value
function add_ten($function-variable) {
  return $function-variable + 10;
}

     

    It does not matter what variable you put into the "use" part of the function. It can be named anything. That is why it's great. You could have a variable called "$another-number" and add ten to that as well. :)

    • Upvote 1

    Having Trouble Understaning The Sort Functions

    in PHP 5 Advanced: Visual QuickPro Guide (2nd Edition)

    Posted

    It's because of the sorting before the echo statements. :)

     

     

    // NORMAL SORTING

    // Print the array as is:

    echo '<h3>Array As Is</h3><pre>' . print_r($students, 1) . '</pre>';

     

    // Sort by name:

    uasort ($students, 'name_sort');

     

    // Print the array now:

    echo '<h3>Array Sorted By Name</h3><pre>' . print_r($students, 1) . '</pre>';

    // Sort by grade:

    uasort ($students, 'grade_sort');

     

    // Print the array now:

    echo '<h3>Array Sorted By Grade</h3><pre>' . print_r($students, 1) . '</pre>';

    • Upvote 1

    How To Make One Database Query From Two?

    in Effortless E-Commerce with PHP and MySQL

    Posted

    Mysqli have functions called mysqli_multi_query mysqli_more_results: http://php.net/manua...ore-results.php

     

    $query  = "SELECT count(*) as count FROM posts";
$query .= "SELECT * FROM posts WHERE posts.status='publish' AND posts.level='".$access_level."' ORDER BY id LIMIT $pageNum, $totalRows";

     

    More than that, I cannot help you really. I haven't played with these functions yet.

     

    The thought is to use the first query to store the number of rows in the database, and the second one to get results.

     

    Hope this help, with the thought process at least. :)

    • Upvote 1

    Prevent Sql Injection Function

    in PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide (3rd Edition)

    Posted

    You don't need mysql*_real_escape_string() with Prepared Statement. I absolutely see your point though

     

    // Set Mysqli or Mysqli
$MySQLI == TRUE;

if ($MySQLI == true) 
{
  // do Prepared statements
} 
else 
{
  self::sanitize_values($input);
  // do things with $value
}

private function sanitize_values($value) {

  // Only remove if magic quotes are enabled
  if ( get_magic_quotes_gpc() ) {
 	$value = stripslashes($value);
  }

  // Then use mysql*_real_escape_string
  $value = mysql_real_escape_string($value);
  return $value;  	

}

    • Upvote 1

    Foreign Keys

    in Effortless E-Commerce with PHP and MySQL

    Posted

    When writing database structures, the normal way to do it is like this:

     

    table (primary_key, foreign key*, other_rows)

     

    It just makes it easier to read. I also like to name foreign keys with tablename_id. Adding fk after the name id_fk is also used to see that it's a foreign key.

×
×
  • Create New...