Jump to content



Highest Reputation Content


#6644 Just Started This Book Today!

Posted by rob on 26 March 2012 - 10:53 AM

It does affect something it affects the source code layout, which is the whole point of using it, to make source code more readable.
  • 4


#3208 Some Tips When It Comes To Mysql Queries

Posted by Antonio Conte on 20 August 2011 - 11:19 PM

1. Always name your tables the same way:
There are best practice rules for naming tables. They should always be lowercase, split by underscore ( _ ) and named in plural. If you need to build tables for several purposes, (forums, shops, fruits) prefix them so the appear next to each other.

Examples:
forums_users
forums_posts
shops_products
shops_prices
shops_availability

2. Use normalization rules(!!!) when creating a structure for tables
Larry explains the different forms of normalization very good in this book. Read it thoroughly, UNDERSTAND it, and plan your tables well. The rules are really not that hard to understand, and will allow you to cross-reference tables in an easy way later on. It will make you understand how the data you are using are working. It will make your systems a lot easier to build on and to introduce new features. I promise you, THIS is how you become a wizard when it comes to working with data in several tables.

3. Use white space(!) and use UPPERCASE for mysql functions (See tip #4!)
When your queries becomes increasingly more complex, you should really follow these tips. To demonstrate, I will give you two versions of the exact same code:

CREATE VIEW view_goals_per_game AS

SELECT league.season AS season,
league.id AS league_id,
league.name AS league_name,
goals.match_id AS match_id,
clubs1.name AS hometeam,
clubs2.name AS awayteam,
players.id AS player_id,
CONCAT( players.fornavn, ' ', players.etternavn ) AS player_name,
goals.goal_time AS goal_time,
games.kickoff_time AS kickoff_time

FROM abc_players_goals AS goals

INNER JOIN cnk_soccer_games AS games ON ( goals.match_id = games.id )
INNER JOIN cnk_soccer_clubs AS clubs1 ON ( games.club1_id = clubs1.id )
INNER JOIN cnk_soccer_clubs AS clubs2 ON ( games.club2_id = clubs2.id )
INNER JOIN abc_players AS players ON ( goals.player_id = players.id )
INNER JOIN cnk_soccer_league AS league ON ( league.id = games.league_id )

LIMIT 0, 1000

create view view_goals_per_game as select league.season as season, league.id as league_id, league.name as league_name, goals.match_id as match_id, clubs1.name as hometeam, clubs2.name as awayteam, players.id as player_id, concat( players.fornavn, ' ', players.etternavn ) as player_name, goals.goal_time, games.kickoff_time from abc_players_goals as goals inner join cnk_soccer_games as games on ( goals.match_id = games.id ) inner join cnk_soccer_clubs as clubs1 on ( games.club1_id = clubs1.id ) inner join cnk_soccer_clubs as clubs2 on ( games.club2_id = clubs2.id ) inner join abc_players as players on ( goals.player_id = players.id ) inner join cnk_soccer_league as league on ( league.id = games.league_id ) limit 0 , 1000

Whick one would you like to maintain? :blink:

4: Save your queries in a text editor

Yes! It sound idiotic, right? It's not. Think of this happening: You accidentally delete, modify or overwrite a query you've used a lot of time on. It will save you a lot of time, tears and the life of a few keyboards! This is also leading up tip number 5.

5. Use views instead of customizing your dataset in PHP(!)
This is a real life-saver. Think of it as including ONE central PHP file instead on chancing 10 documents every time you make a change. The views should be written to display AS MUCH information as possible. Try to think of every scenario you may want to use the data. Views are, with a few exceptions, for displaying data; hence it's name.

NOTE: After the creation of a view, you CANNOT modify it. This is why you should follow step 4.

- Views are really easy to create:
[u][i]CREATE VIEW the_name_of_the_view AS[/i][/u]
SELECT *
FROM table1

The query used in tip #3 is a view. It would allow you to sort by a specific league, match_id, player_id or by kickoff_time. The view is used to display statistic about the goal scorer in a football match. (soccer for americans) The table for saving goal statistic has three rows(!). Match_id, player_id and goal_time. This is good data normalization, and minimizes redundancy and make for consistent data. That means the table players_goals need to be linked with other tables that holds data about the specific match and the specific player. This is the reason for create a view.

Don't see the point of this?
There really is one - I promise! By writing a view that is general and display a lot of data, I can write SIMPLE queries to get different results:

1. Last five goal scorers with name of both teams, player name and goal time
SELECT hometeam, awayteam, player_name, goal_time
FROM view_goals_per_game 
ORDER BY kickoff_time DESC

2. Display players with most goals in descending order
SELECT player_name, COUNT(*) as goals
FROM view_goals_per_game
GROUP BY player_id
ORDER BY goals DESC

3. Display all goals by a specific player
 SELECT hometeam, awayteam, player_name, goal_time
FROM view_goals_per_game
WHERE player_id = 10
ORDER BY kickoff_time DESC

4. Find all goals in a specific match
SELECT hometeam, awayteam, player_name, goal_time
FROM view_goals_per_game
WHERE match_id = 837

Does it start to make some sense? Instead of repeating and modifying the same hard-to-grasp code over and over again (see tip #3), use a view to make your life simpler. :)

Hope this has helped someone. I have been thinking about writing a lot of different tips when it comes to MySQL. I've been thinking about writing a guide to joins for example. I really don't know if this interests someone or not.

Hoping others will also share tips and experiences when it comes to MySQL. :)
  • 4


#14402 Really Disturbing

Posted by margaux on 1 May 2013 - 4:56 PM

Hey xto, I'm going to try to say this in the nicest way possible -  You're in danger of trying people's patience not because you ask questions, we like questions, but because you ask questions in a way that doesn't provide the information needed to try to answer them.

 

1. please read the forum guidelines - Look for the little grey text bottom right of most pages, labelled Guidelines

2. please post only RELEVANT code and error messages within code tags. We don't need to see the entire output from your error message. It's actually distracting. Use code tags which are on the edit bar and they look like <>

3. post the relevant CODE, you keep posting the error message but not any code.

4. You should start a new thread for each new question. One reason for doing so is that other people with the same question can search and find your thread. If your question is part of another thread, it won't be found and won't help others.

5. You're asking questions that you should be able to solve 1 because the level of  experience for this book expects you know some basic debugging strategies and 2. with a little bit of online searching you would get some pointers as to where to look for the cause of your error.

 

I really shouldn't answer your question given the above but ...

 

somewhere in your code you are referencing an array value using 'sale_price' as the index, which doesn't exist. I'm going to hazard a guess that you have a line that includes $row['sale_price']. From the error dump you posted, you will see there is no index 'sale_price' but there is one named 'price'. Given what info you've provided that's all I can help with. If this doesn't help solve the problem, start a new thread and post the code that is causing the error :)


  • 4


#8626 Chapter 3 - Returning Values From A Function

Posted by HartleySan on 12 July 2012 - 5:54 PM

When you call the calculate_trip_cost function (as is the case on line 48 in the script), the three arguments specified within the parentheses and separated by commas are mapped to the three parameters (also sometimes called arguments; it doesn't really matter) within the parentheses in the function definition header on line 24.

In the code in the book, this corresponds to the value in $_POST['distance'] being mapped to the parameter $miles, the value in $_POST['efficiency'] being mapped to $mpg, and the value in $_POST['gallon_price'] being mapped to $ppg. (And when I say mapped, I simply mean that the values in the arguments in the function call are copied to the corresponding parameters in the function definition header.) By doing this, you can use values that were set or established outside of the function within the function, which relates to the variable scope discussion.

As another example, if I wrote the function call calculate_trip_cost(1, 2, 3), then 1 would be set for $miles, 2 would be set for $mpg, and 3 would be set for $ppg.

As for how the $_POST array contains values in the first place, that relates to other discussions earlier in the book. Basically though, when you submit an HTML form (with the post method), PHP is designed to automatically store all values within the form into the $_POST superglobal associative array (just to throw some other vocab out there for you).

Does all that make sense? Please let me know if it doesn't.
  • 3


#20294 Php/mysql Error Message?

Posted by Emilie on 19 August 2014 - 7:53 AM

Hello,

 

MySQL doesn't 'know' the variables you created with PHP. In order to test your query, you need to replace the variables with their values.

 

I hope this helps,

 

Emilie


  • 3


#15287 Use Data Type Enum For Form Drop-Down Options

Posted by margaux on 2 July 2013 - 3:10 PM

You're close. You have not stored your result anywhere with mysqli_fetch_array, so you won't be able to access the returned data.

$table_name = "collection";
$column_name = "PENDING";

echo "<select name=\"$column_name\"><option>Select one</option>";
$q = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = '$table_name' AND COLUMN_NAME = '$column_name'";
$r = mysqli_query($dbc, $q);

$row = mysqli_fetch_array($r);

$enumList = explode(",", str_replace("'", "", substr($row['COLUMN_TYPE'], 5, (strlen($row['COLUMN_TYPE'])-6))));
foreach($enumList as $value)
    echo "<option value=\"$value\">$value</option>";

echo "</select>";

  • 3


#14154 Multiple Mysql_Fetch_Array That Drives Me Nuts

Posted by HartleySan on 18 April 2013 - 3:12 PM

Hmmm... I'm not entirely sure what you're going for, but I'm definitely seeing some serious inefficiencies. While I don't know the sizes of your tables (i.e., how many records each one contains), you seem to be grabbing everything from the purchases table, and within that while loop, you are then grabbing everything from the other two tables where a certain value equals a certain value in the purchases table.
 
Also, a lot of the math you're doing (for example, adding up values for the amount of a given order) and formatting of the date can be done on the DB side, which can further speed things up.
In general, I think your goal should be to format all your data exactly the way you want to print it out to the screen by using one query. That may not be possible, and I'm not entirely sure what you want, but I'm thinking that we *may* be able to get everything you want in one query. I will attempt to do so below, but I can't guarantee that it'll work.
 
To start with, I'm trying really hard to figure out what exactly you're going for, but it's a bit abstract with the variables you're using. I'm not sure if you're using those variables on purpose to disguise your code for this thread, or if you're really using those variables in your code, but either way, I would definitely recommend using more logical variable names.
 
Anyway, here's my interpretation of your code:
You're printing out a table of purchase orders. The first column is the row number (which you do not seem to be properly incrementing within the outer while loop). The second column is the ID of a purchase order that a customer has made. The third column is the name of the customer (and it looks like you're storing their first and last name in one column in the customers table, which I would recommend against). The fourth column is the date and time of the purchase. The fifth column is the total amount of the purchase order formatted in dollars and cents. Lastly, I'm not sure what the last two columns are, but they aren't coming from DB data, so I'll ignore those for now.
 
Assuming my interpretation above is correct, I think you need to do an inner join across three tables with the purchases table being the main table. Also, I think you need to group your purchases together by the purchase ID, so that you can use an aggregate function to add up the price of the individual items within each specific order.
Does that make sense?
 
Anyway, here's the query that I'm *thinking* will work (but I can't guarantee that it does or that it's what you want):
SELECT c.cust_id, c.name AS cust_name, p.po_id AS order_num, p.cust_id, DATE_FORMAT(p.timestamp, '%m %d, %Y') AS date, o.po_id, SUM(o.delivered * o.srp) AS amount
FROM customers AS c, purchases AS p, po_content AS o
WHERE c.cust_id = p.cust_id AND p.po_id = o.po_id
GROUP BY o.po_id
ORDER BY p.timestamp ASC
LIMIT $start $display;
 
A few notes about the query:
1) For your query, a join is essential. Specifically, two inner joins on the purchases table is what you need. Joins are tricky at first, but they're essential for most DBs, so I'd recommend studying up on them.
 
2) Only select the columns you need. Using SELECT * for three separate queries is getting you a lot of data you don't need, and is very inefficient.
 
3) Use aliases (e.g., "AS c", "AS p", etc.) on the tables to make typing out the query shorter and easier. Also, aliases are essential for being able to easily reference the results of aggregate functions, formatted, dates, etc.
 
4) Format the timestamp on the SQL side using the DATE_FORMAT function. It's faster and easier. Also, give the formatted date an alias to make it easier to access on the PHP side. Here's more info on the DATE_FORMAT function:
 
5) I'd calculate the total amount of each order on the SQL side. To do so, you need to use the SUM aggregate function, and also use the GROUP BY clause to group your results together by order number so that you are adding up the correct grouping of items. Also, I'd assign an alias to the result of the SUM function.
 
6) The "ASC" part of the query is not necessary, since that's the default ordering. I left it anyway to avoid any further confusion.
 
7) I used "o" as the alias of the po_content table, as it seems like a table of orders to me.
 
That will hopefully handle the query side of things.
Unfortunately, I think there are some other issues with your code as well:
1) You're not incrementing $rownum in the while loop.
 
2) You're assigning your $bgcolor value to the entire table, not individual table rows. Also, I'm pretty sure the resulting HTML will be syntactically invalid and not work.
 
3) You're creating a new table each time through the while loop.
 
4) You're putting a div within a td for the total, which I wouldn't do.
 
Point being, without sounding too harsh, I think your code has some serious issues and needs some re-working. I get the feeling that you might be getting a bit too ambitious about your personal project without first understanding all the basics you need.
I don't mean to say that you shouldn't be ambitious, but I think you should probably go back to the book for a bit and bone up on queries with joins, HTML and PHP syntax, as well as think more about the logic of your while loop and the type of HTML that it is creating.
 
Anyway, below, I'm going to present the PHP I would use for your situation. Again, please keep in mind that I'm doing my best to piece together exactly what you want (and I'm not entirely sure), so I could be way off on this.
// I'm assuming that $agent and $encoder are already defined above.
 
$row_num = 1;
 
$total = 0;
 
$bg_color = '#FFF';
 
$q = "SELECT c.cust_id, c.name AS cust_name, p.po_id AS order_num, p.cust_id, DATE_FORMAT(p.timestamp, '%m %d, %Y') AS date, o.po_id, SUM(o.delivered * o.srp) AS amount FROM customers AS c, purchases AS p, po_content AS o WHERE c.cust_id = p.cust_id AND p.po_id = o.po_id GROUP BY o.po_id ORDER BY p.timestamp ASC LIMIT $start $display;";
// I'm assuming that $start and $display are already defined above.
 
$r = mysqli_query($dbc, $r);
// I am assuming that $dbc is already defined above. You also seem to have omitted this argument in your code.
 
echo '<table class="order_details">';
// I'd use CSS to properly format the table instead of the inline attributes you're using.
// As such, I have assigned a class to the table for that exact reason.
// Also, your table doesn't have any headers, but you may want to add them along with thead, th, tbody, and tfoot tags.
 
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
  
  echo '<tr style="background-color: ' . $bg_color . ';">
  
  <td>' . $row_num . '</td>
  
  <td>' . $row['order_num'] . '</td>
  
  <td>' . $row['cust_name'] . '</td>
  
  <td>' . $row['date'] . '</td>
  
  <td>' . number_format($row['amount'], 2) . '</td>
  
  <td>' . $agent . '</td>
  
  <td>' . $encoder . '</td>
  
  </tr>';
  
  $row_num++; // Don't forget to increment this.
  
  $total += $row['amount']; // This is the summation of the unformatted amounts, which could cause issues.
  
  $bg_color = ($row_num % 2 === 0) ? '#F3F3F3' : '#FFF'; // Ternary operation for brevity
  
}
 
echo '<tr style="background-color: ' . $bg_color . ';">
 
<td colspan="4" class="total_row">Total</td>
 
<td>' . number_format($total, 2) . '</td>
 
</tr>';
// Formatted to line up with the amount column.
// Also, note that I handled the total the same way you did, but if you calculate the total on the
// unformatted amount values, then you may get a discrepancy in which the amounts don't add up to the total.
// Also, again, I'd use CSS (not inline HTML) to align "Total" to the right.
// Lastly, you may want to put the total in a tfoot element.
 
Well, I think that's about it.
After all this writing, I really hope that I got close to what you wanted, and that this post is of some use.
Please let me know.
Thanks.

  • 3


#13111 Losing $_Get Values After Pressing Reload Button

Posted by HartleySan on 19 February 2013 - 9:54 AM

Hello, Roy. Welcome to the forums. I hope you find them helpful.

I think you have a few options:

 

1) Store the $ordernbr value in a hidden input in the form on the second page, and then grab that value from the $_POST superglobal when you post the form with the "Reload" button.

 

2) Save the $ordernbr into a cookie or session.

 

3) Use JavaScript and Ajax to change part of the form dynamically without actually reloading the page. (This is rather advanced and not really recommended. I more am just presenting it here because it is a possibility.)

 

Of the three options, I probably wouldn't attempt #3 unless you want a serious challenge.

#2 is a very viable option, but I'm not sure if Larry talks about cookies/sessions in the book, so I'd check that first.

So that leaves us with option #1.

 

Basically, when you are using PHP to write the HTML for the form on the second page, add am extra hidden input element (I think Larry talks about these in the book) with a value equal to the $ordernbr variable. That way, when you post the second form by clicking the "Reload" button, that $ordernbr value will be posted and accessible in the $_POST superglobal under the name set for the hidden input.

Does that make sense?


  • 3


#12357 Geting Url Form Users And Validate Them

Posted by HartleySan on 16 January 2013 - 3:14 AM

Edward, a little bit less of a Yii sales pitch would have been nice.

thara, you said you tried to use FILTER_VALIDATE_URL in PHP. Did it work? It should.
I would use FILTER_VALIDATE_URL, if possible, and as you suggested, I might omit the protocol entry part, as all links should probably be either http or https.
  • 3


#11574 Please Explain Routing Rules Precisely !

Posted by HartleySan on 8 December 2012 - 12:11 PM

Ziggi, sometimes when we try to help people on this forum, we don't get it right the first time.
Quite often, the reason someone is asking a question and the reason they don't understand something is not very clear to us. We don't intentionally try to not answer someone's question.
Antonio and Edward were both doing the best they could to help you. The fact that they (unintentionally) missed the mark and didn't give you the answer you wanted right away is no reason to act the way you did.

A more civil approach would have been to reanalyze your original post(s), think about why Antonio and Edward perhaps weren't able to answer your question, and then politely reprhase your question to hopefully reach a reasonable solution eventually. I guarantee that if you had been polite about it, both Antonio and Edward would have been more than happy to continue helping you until you got the answer you were looking for.

It seems like you're not a native English speaker, so perhaps the source of the arguments was all just a simple misunderstanding on both sides, but at this point, you really have no hope of ever getting any more relevant information to your question. And more than likely, you've ruined any chances you have of ever getting any help on this forum again. Both Antonio and Edward are the two most active and knowledgeable posters on the Yii forum here, so you're probably SOL.

I think you have one of two choices:

1) Apologize for your previous posts, and with time, I think tempers will cool down and we can get back to having civil discussions and everyone can get answers to questions and be happy. Please note that I am not asking you to beg on your knees, but at this point, I think a sincere apology would be nice.

2) Don't post on this forum anymore and consider this a lesson learned for future interactions on forums.

The choice is yours. I also had to swallow my pride recently and apologize for stepping over the line on this forum, and even though it didn't feel good at the time, I'm glad I did apologize and everyone was able to go back to being civil and helping out again. I'd recommend the same for you.
  • 3


#11560 Simple Question On Php Form Validation...

Posted by HartleySan on 8 December 2012 - 12:32 AM

When you execute the header(Location: 'somelocation.php#contact'); line, you're reloading the page, which causes the $_POST array, $errors variable, etc. to no longer exist. This essentially amounts to the same thing as having never performed any validation at all (and thus the $errors variable not existing, which is why your "Please fix the errors" line is never printed out).

A better solution would be to put the anchor jump (i.e., the fragment part in the URL after the number sign (#)) in the URL specified for the action attribute of the contact form. For example, if the following is the opening tag for your contact form:

<form action="somelocation.php" method="post">

Then change it to the following:

<form action="somelocation.php#contact" method="post">

Of course, this assumes that somelocation.php refers to the original page the form is on (which seems to be the case according to what you said).
By doing the above, you can very easily get the jump you want and still properly validate everything.

In terms of outputting a thank you message upon successful validation, I'd do something like the following:

if (!$errors) {

  // Validation was successful. Output a thank you message.

} else {

  // Either there was an error or this is the first time the page was loaded.
  // Load the page with the form and any necessary sticky values and error messages.

}

Hope that helps.
  • 3


#10870 Page 303

Posted by margaux on 2 November 2012 - 4:42 AM

I would have thought the error in the query was due to the ORDER BY clause. In your query you create an alias for the date_registration by do not use it in the ORDER BY clause. It should be
$q = "SELECT last_name, first_name,
➝ DATE_FORMAT(registration_date, '%M
➝ %d, %Y') AS dr, user_id FROM users
➝ ORDER BY dr ASC
➝ LIMIT $start, $display";

Its definitely worth spending time on the debugging chapter - you will save yourself loads of time in the future and learn a few tricks. One quick trick is to insert something like
print_r($r)
in your code which will give you full information about your query in a more readable format.
  • 3


#10752 Chapter 8: Page 290 Referencing The Event Section

Posted by Antonio Conte on 29 October 2012 - 7:02 PM

You'll see this often in programming if the function uses well known functionality. It's much clearer with parameter hinting as you get the object type to. It's also more common if classes implement well-known interfaces to get functionality. Below is an example from Java that allows to to get input from a mouse. These types of interfaces can include 15-20 methods, so you would spare a lot of code by just using Event e.

class MouseInput implements MouseListener
{
   public void mouseClicked( MouseEvent e ) { }
   public void mouseEntered( MouseEvent e ) { }
   public void mouseExited( MouseEvent e ) { }
   public void mousePressed( MouseEvent e ) { }
   public void mouseReleased( MouseEvent e ) { }
}

AnyEvent e is pretty much standard naming for events, as is Graphics g, Comparator cmp or Iterator itr. These of course differ slightly in difference languages, etc, but are pretty general well known names for the functionality they provide. These names are not "standards" by any stretch of the imagination, though, so feel free to use what you like.
  • 3


#10152 Pass A Multi Dimensional Array To Hidden Forms

Posted by HartleySan on 24 September 2012 - 6:26 AM

If you really want to, you can do something like the following:

<!DOCTYPE html>

<html lang="en">

  <head>

    <meta charset="UTF-8">

    <title>Test</title>

  </head>

  <body>

    <?php

      if (isset($_POST['submit'])) {

        echo '<pre>';

        print_r($_POST['data']);

        echo '</pre>';

      }

    ?>

    <form action="" method="post">

      <input type="hidden" name="data[]" value="0">

      <input type="hidden" name="data[0][]" value="Cheese">

      <input type="hidden" name="data[0][]" value="Bacon">

      <input type="hidden" name="data[]" value="1">

      <input type="hidden" name="data[1][]" value="Peppers">

      <input type="hidden" name="data[1][]" value="Fork">

      <input type="submit" name="submit" value="Submit">

    </form>

  </body>

</html>

However, I'm having trouble coming up with a reason you'd want to do that. As rob said, storing that data in a session would probably be better.
  • 3


#20304 Php/mysql Error Message?

Posted by Emilie on 20 August 2014 - 6:55 AM

Hello,

 

Another typo:

ContractWitnessed='0,

The closing quotation mark is missing.

 

Also, if your ID is numeric, you should not quote it:

where (ID='23')

 

If this doesn't do the trick, I would suggest testing a few lines only at a time, until you find where the query goes wrong.

 

I hope this helps,

 

Emilie 


  • 3