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

    Mysqli_Query() Error: Syntax Error

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

    Posted

    Hello,

     

    I'm struggling to post data into the database. As the VAR_DUMP() explains, I suspect is has something to do with values beeing sent as STRINGS instead of INT.

     

    The script is generating a text query that WORK in PhpMyAdmin. (I echo the variable $insert_info after mysql_query($insert_info)

     

    -------------------------------------------

     

    The PHP code for producing the SQL-query looks like this:

     

    /* BUILDS QUERY FROM $_POST[]
* - The first if is looking for a submit with value "Lag Statistikk"
* 
*/
if ($_POST['querygen'] == "Lag statistikk") {

	/* Create a string for the Query. 
		* The string is buildt with concatenation to this string ( .= )
		* Each multi-dimensional $_POST-array starts from Zero....
		* and the for-loops are based on an earlier set "number_of_X" - $_POST-values"
		*/

	// Insert for table abc_players_captain
	$_POST[giornata]   	= (int) $_POST[giornata];
	$_POST[players_captain] = (int) $_POST[players_captain];
	$info_insert = "INSERT INTO abc_players_captain (match_id, captain_id) VALUES ({$_POST[giornata]}, {$_POST[players_captain]});";

	// Insert for table abc_players_goals
	$_POST['number_of_goals']= (int) $_POST['number_of_goals'];
	for ($i = 0; $i < $_POST['number_of_goals']; $i++) {
		if (isset($_POST[goal][$i])) {
			$_POST[goal][$i][goal_id]   = (int) $_POST[goal][$i][goal_id];
			$_POST[goal][$i][goal_time] = (int) $_POST[goal][$i][goal_time];
			$info_insert .= "INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES ({$_POST[giornata]}, {$_POST[goal][$i][goal_id]}, {$_POST[goal][$i][goal_time]});";
		}
	}

	// Insert for table abc_players_assists
	$_POST['number_of_assists'] = (int) $_POST['number_of_assists'];
	for ($i = 0; $i < $_POST['number_of_assists']; $i++) {
		if (isset($_POST[assist][$i])) {
			$_POST[assist][$i][assist_id] 	= (int) $_POST[assist][$i][assist_id];
			$_POST[assist][$i][assist_time] = (int) $_POST[assist][$i][assist_time];
			$info_insert .= "INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES ({$_POST[giornata]}, {$_POST[assist][$i][assist_id]}, {$_POST[assist][$i][assist_time]});";
		}
	}

	// Insert for table abc_players_cards
	$_POST['number_of_cards'] = (int) $_POST['number_of_cards'];
	for ($i = 0; $i < $_POST['number_of_cards']; $i++) {
		if (isset($_POST[card][$i])) {
			$_POST[card][$i][card_id]   = (int) $_POST[card][$i][card_id];
			$_POST[card][$i][card_time] = (int) $_POST[card][$i][card_time];
			$_POST[card][$i][card_type] = (int) $_POST[card][$i][card_type];
			$info_insert .= "INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES ({$_POST[giornata]}, {$_POST[card][$i][card_id]}, {$_POST[card][$i][card_time]}, {$_POST[card][$i][card_type]});";
		}
	}

$query_result = mysqli_query($connect, $info_insert);

// Display success/error-message depending on TRUE/FALSE of $query_result
if ($query_result) {
	echo '<h2>Sucess</h2>';
}
else {
	echo '<h2>Query Failure</h2>
			'.mysqli_error($connect).' <br /> <br />
			<pre>'.$info_insert.'</pre>
	';	
}

// CLOSE connection
mysqli_close($connect);

} // IF ISSET($_POST[]) END

     

    -------------------------------------------

     

    Echo $insert_info and Mysql-error:

     

    FAILURE:
You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near '
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868' at line 1 

THE QUERY FOLLOWS:
INSERT INTO abc_players_captain (match_id, captain_id) VALUES (868, 10); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 10, 30); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 9, 78); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 27, 85); 
INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES (868, 3, 30); 
INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES (868, 23, 85); 
INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES (868, 4, 4, 1); 
INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES (868, 9, 67, 1);

     

    -------------------------------------------

     

    The table structures looks like this:

    abc_players_captain (match_id, player_id*)

    abc_players_goals (match_id, player_id*, goal_time)

    abc_players_assists (match_id, player_id*, assist_time)

    abc_players_cards (match_id, player_id*, card_type, card_time)

     

    ALL columns are INT of different lengths. I've tripple-checked both the names and lengths of the columns to assure thats not the problem.

    The query inserts data correctly when using phpMyAdmin as said earlier

    Underlined: Primary key

    Asterix (*): Foreign key

     

    -------------------------------------------

    This is the var_dump of the $_POST-array:

     

    The for-loops in this script is based on $_POST-values number_of_goals, number_of_assists and number_of_cards. All these loops are working, as you can see by the query. (see below)

     

    array(9) {
["giornata"]=>
int(868)
["players_captain"]=>
int(10)
["number_of_goals"]=>
int(3)
["number_of_assists"]=>
int(2)
["number_of_cards"]=>
int(2)
["goal"]=>
array(3) {
	[0]=>
	array(2) {
		["goal_id"]=>
		int(10)
		["goal_time"]=>
		int(30)
	}
	[1]=>
	array(2) {
		["goal_id"]=>
		int(9)
		["goal_time"]=>
		int(78)
	}
	[2]=>
	array(2) {
		["goal_id"]=>
		int(27)
		["goal_time"]=>
		int(85)
	}
}
["assist"]=>
array(2) {
	[0]=>
	array(2) {
		["assist_id"]=>
		int(3)
		["assist_time"]=>
		int(30)
	}
	[1]=>
	array(2) {
		["assist_id"]=>
		int(23)
		["assist_time"]=>
		int(85)
	}
}
["card"]=>
array(2) {
	[0]=>
	array(3) {
		["card_id"]=>
		int(4)
		["card_time"]=>
		int(4)
		["card_type"]=>
		int(1)
	}
	[1]=>
	array(3) {
		["card_id"]=>
		int(9)
		["card_time"]=>
		int(67)
		["card_type"]=>
		int(1)
	}
}
["querygen"]=>
string(14) "Lag statistikk"
}

     

    -------------------------------------------

     

    Hope you can help me solve this weird error. I just don't get it. <_<

     

    Btw: I'm well aware of the dangerous of using $_POST[] directly in a script. This is just used by a few people behind a htaccess-restricted directory. The security is not as important at the moment as getting this to work. Please feel free to comment on that to if you'd like, but It's not the main focus right now.

    Chapter 2: Variable Decleration

    in PHP for the Web: Visual QuickStart Guide (4th Edition)

    Posted

    In some other languages, like Java, you have to declare a variable before you use it. What declaring them really does, it allocating a place in the memory for the information you save in a variable. It might be a bit technical, but you should not really care why it's like this. Just understand how to use variables.

     

    This is Java with predeclared variables:

     

    String firstName;
String lastName;
String adress;
Int phone;
Int age; 
Int credit_card_number = null;

// now we can use these variables below in the script.

firstname = "Thomas";
lastname = "Lastname";
String fullname = (firstname . ' ' . lastname);
// echo statement

     

    PHP also is a loosly typed language, unlike Java. That means you don't have to set the type of information the variable should save (numbers, text, booleans, etc). It's done automaticly, like declaration is.

     

    $firstname = "Thomas";
$lastname = "Lastname";
$fullname = $firstname . ' ' . $lastname;
echo $lastname

    The First Query In The Message Board Example

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

    Posted

    Shoun. I tried to sort on datetime column now. Max(date_column) is returning the most recent date. Min(date_column) is returning the oldest date.

     

    Date_format is for displaying dates different. You could use European form like (dd.mm.yyyy) or anything you want. It's only for display.

     

    CONVERT_TZ() is for timezone conversions. It will allow you to use a GMT datetime and add/subract automaticly to your timezone. Do you store the user's timezone? Else, it's useless for you.

     

    This will do what you need:

     

    SELECT DATE_FORMAT(MAX(posted_on), '%e-%b-%y %l:%i %p') AS last, DATE_FORMAT(MIN(posted_on), '%e-%b-%y %l:%i %p') AS first
FROM table_with_your_posts

     

    I have tested it myself, and it works perfectly with my data. (32 football players with birthdates saved) The problem was that MAX() and MIN() calculates the dates wrong if it's formated BEFORE finding the MAX and MIN. Because the query starts with the most inner function, this would be the right way to do it. If you really need timezone conversions, add it inside the MAX() and MIN()-functions to get it correct. :)

    The First Query In The Message Board Example

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

    Posted

    You only need the most recent post? Forget about that code, and do it all in the query

     

    SELECT *
FROM table_with_posts
ORDER BY posted_on DESC
LIMIT 1

     

    That should produce the latest post in the table according to post date. Same for first post. use ASC instead of DESC in the order by-clause. Replace the astrix (*) with the columns you would like to display, and alter the table name. Then you are ready to go.

     

    If you need usernames, etc, a simple join is enough.

     

     

    SELECT posts.*, users.*

FROM table_with_posts AS posts
INNER JOIN table_with_users AS users ON (posts.user_id = users.users_id)

ORDER BY posted_on DESC
LIMIT 1

     

     

    Edit: 100 posts. Enjoying the moment. Moment's passed. Life goes on. :P

    • Upvote 1

    Icons/Colors After Checkbox/Option I Choosen

    in Building a Web Site with Ajax: Visual QuickProject

    Posted

    Hey everyone

     

    First off; I have NO knowlage of Ajax. I do not intend to learn it as this point neither. I'm working of saving huge amount of football statistics, but I still need the powers of Ajax!

     

    I hope you will let this one slide. ;)

     

    I'm displaying information about the players this way:

     

    <div style="width: 100%; float: left;">
<p><input type="checkbox" name="player[0][id]" value="1" />Gianluigi Buffon</p>
<input type="text" name="player[0][time]" value="" />
<select name="player[0][sub]">
	<option value="0">Full match</option>
	<option value="1">Subbed Inn</option>
	<option value="2">Subbed Out</option>
</select>
</div>

<div style="width: 100%; float: left;">
<p><input type="checkbox" name="player[1][id]" value="13" />Alexander Manninger</p>
<input type="text" name="player[1][time]" value="" />
<select name="player[1][sub]">
	<option value="0">Full match</option>
	<option value="1">Subbed Inn</option>
	<option value="2">Subbed Out</option>
</select>
</div>

.........

.........

     

     

    What I want to achive:

    - Every clicked checkbox gets a BLUE icon to the right

    - Players subbed inn gets a GREEN icon to the right

    - Players subbed out gets a RED icon to the right.

    Something like this:

    926097.jpeg

     

    Is this possible with Ajax? Anyone willing to help a forum member in need out with this? :)

    Varbinary? Trouble Setting Up The Database In Phpmyadmin

    in Effortless E-Commerce with PHP and MySQL

    Posted

    Varbinary should be varchar(32). I actually have a Varbinary in my setup. It looks like it is like a number of binaries (zeroes and ones)

     

    You don't need to change your databas type. You can do this be clicking the table, selection "Operations", and select a different DB type. There are some benefits and some drawbacks regarding both, so it really doesn't matter else your told to use a spesific type.

     

    Unique is kind of like Primary key. It means it cannot exist similar data. You can do a combined unique with several columns. A good suggestion is often an auto_increment Primary key and a combined Unique to make sure data cannot be saved twice. (If you need to make sure of this)

     

    Key could be several things. Primary keys, foreign key and possibly some others. A foreign key means a refferance to another tables Primary key. If you ever wanted to use user_id in another table, say sales, the user_id would be a foreign key to the users table.

     

    Index and Full Text is used for database optimalization. If a number (lets say user_id, user_name rows is often used in queries, an index would speed up queries when both these coulumns are used. Full Text is used to speed up long columns of text. That is good if you need to perform searches on text. A good example is you need to search in forum post. Full text is made for this.

     

    Hope this answeers some of your questions. :)

    Persistent Objects In Forms

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

    Posted

    If your SQL is bloated, that would probably call for some inner joins and creation of views to make your job easier. The syntax for Inner joins are pretty easy,

     

    CREATE VIEW main_view AS

    SELECT main.*, (other rows)

    FROM main_table as main

    INNER JOIN second_tale ON (main.id = second.id)

     

    .....

     

    When you have written the views you like, SELECT * FROM main_view is enough.

     

    You cannot delete/edit or add to every view. It's for display, as the name suggests.

    Num Of Weeks And Date Between Two Dates (And A Num Of Days)

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

    Posted

    I LOVE MYSQL! It's always a challenge to do new things, but you can really see the benefit of learning how to do things.

     

    I use the following query to find the WHOLE number of weeks and days before the injury is finished:

     

    SELECT player_id,

    ROUND((datediff( IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date`) , `injury_date`) / 7 )) AS weeks,

    ROUND((datediff( IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date`) , `injury_date`))) - (ROUND((datediff(IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date` ) , `injury_date` ) /7 )) *7) AS days

    FROM `abc_injuries

    Edit:

     

    This would be right :)

     

    SELECT player_id, ROUND( (datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  /7  )  - 0.5) AS weeks, (ROUND( (datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  ) ) - ( ROUND( (datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  )  )  - ROUND( datediff( IFNULL( date_add(  `recovery_date` ,  INTERVAL  `variable` 
DAY  ) ,  `recovery_date`  ) ,  `injury_date`  )  %7  ) )) AS days
FROM  `abc_injuries` 
LIMIT 0 , 100

     

    I tried to explain how this works. I just realized I can't. :huh::lol:

     

    Edit 2: Had to subtract the number of weeks with 0.5 to prevent 6 extra days to become for example 3.8 days and rounded up to 4 weeks. This is in the first part of the query (the AS weeks part)

    Insert Data Into A Database

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

    Posted

    Hey Paul

     

    It would solve you problems, yes, but using quotes is probably easier. I understand what you say about threes and the forest, and I agree with you. Database systems need to work, that's the most important. If you get lost by the structure, it's probably a little too tricky for you.

     

    Integers don't need quotes in queries. Text does. The name DROP is likely the problem, but might work with quotes. try curtain_drop our something instead if it does not work. :)

    Insert Data Into A Database

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

    Posted

    May I make some suggestions about your structure? I know very little about curtains, but I see you use a lot of "hard coded values".

     

    Have you thought about making additional tables to keep data integrity in place? regular_lining, three_inch, etc is possible to misspell (and not so easy to work with)

     

    Lets say you add a table lining:

    lining_id (auto_increment, primary key)

    lining_type (Varchar)

     

    It would allow you to use lining_id as the value to save in your database. It would also make number functions easyer. Like to show how many curtains with regular lining you have. This can be extended to most of your other things to.

     

    SELECT lining_type, count(lining_id)
FROM lining
WHERE lining_id = 8
GROUP BY lining_type asc, lining_id asc

     

    Example output:

    Regular lining (15 available)

    Silk lining (3 available)

    Ragged lining (7 available)

     

    Just a suggestion. :)

    Num Of Weeks And Date Between Two Dates (And A Num Of Days)

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

    Posted

    Hello everyone.

     

    Time for another challenge. This time, I need to save stats about player injuries. I need some help with the thought process here.

     

    I'm thinking abot the following structure:

    - Players ID (INT)

    - injury date (Datetime)

    - ready to play date (Datetime)

    - Day fixer (INT, default null)

    - Type of injury (String (OR foreign keys to injury_type table))

     

    -------------------------------------------

     

    What I need with the data:

    - Calculate the number of weeks before the injury is over. I also think about using the field additional days for the possibility of adding/subtract a number of days for when the injury is over.

     

    -------------------------------------------

     

    Hope some of you know mysql date functions. I'm want to calculate the number of weeks and days between INJURY DATE and (FIT DATE, plus/minus a number of days).

     

    How it will be displayed:

    - Player name

    - Player injury type

    - Ready in (about) X number of weeks/(days if less than a week)

     

    Thank you in advanced. I will use some time to think about how to solve this. :)

    Help Creating Queries With Php

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

    Posted

    That's 100% right and the way I've done things, abigail. Don't know if you tried to help our just explained it, but thanks anyway. :)

     

    I've actually solved this issue, but I and HartleySan are just discussing table structures.

     

    Larry could label this is [sOLVED] if he cares.

     

    Thanks everyone for your help. I could explain more over PM or even MSN/Skype if you want HartleySan.

×
×
  • Create New...