mysql_query problem connecting to the database

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 1704 Views - Last Post: 20 October 2012 - 07:48 AM Rate Topic: -----

#1 Hiyall   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 05-October 12

mysql_query problem connecting to the database

Posted 19 October 2012 - 10:55 AM

Hello all.
I have a problem that I think is due to a problem connecting to the database and it goes like this:
I want to make a button that highers a value inside a column named "clicks" by 1. So when I press this button, the database will plus the current value in the database with 1.
So what I have done is I have made a table named "pbclickslogin" with a column named "clicks". When the user press on a submit button, the database will go inside "pbclickslogin", find the column "clicks" and add 1 to it.
My code goes like this:
<?php
if (!empty($_SESSION['id'])) {
 
echo ('<p>You have <strong>' . $_SESSION['clicks'] . '</strong> Clicks</p>');

}

?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<?php

$idsession = $_SESSION['id'];

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

 		$result = mysql_query("SELECT * FROM pbclickslogin WHERE id = '$idsession'");
		while($clicked2 = mysql_fetch_array( $result ))
		{
		$clicked = $clicked2['clicked'] + 1;
		
        $query = "INSERT INTO pbclickslogin (clicks) VALUES ('$clicked')";
        mysqli_query($dbc, $query);
		}
}
?>
<input type="submit" value="Click!" name="click" />
</form>


I have checked that $idsession works.
But it prints this to me:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/blablabla.
And it does not add 1 to the database.

I hope you can help me, if I did not explain this detailed enough or you think I have missing content, please tell me and I'll provide you with it right away!

- HIYAA!

Is This A Good Question/Topic? 0
  • +

Replies To: mysql_query problem connecting to the database

#2 lukasz_ltt   User is offline

  • New D.I.C Head

Reputation: 18
  • View blog
  • Posts: 29
  • Joined: 12-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 10:59 AM

1. You shouln't use mysql_query - it's deprecated
2. Your result is boolean because, your query is invalid or returns nothing
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4278
  • View blog
  • Posts: 13,563
  • Joined: 08-June 10

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 11:00 AM

your query fails. itís as simple as that.

is the id column really a string type?

anyways, why not using an UPDATE query?

and further, if the id is a unique value, then you donít need a while loop. ah, and mysqli and mysql are not interchangeable.
Was This Post Helpful? 1
  • +
  • -

#4 Hiyall   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 05-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 11:08 AM

If I cannot use mysql_query, then what can I use that is better but gives the same effect?
The UPDATE query sounds like a good idea since I'm just updating the column but how would I implement that and do I not need to use a while loop as you say since my id is a primary key?

Thanks for the quick answers!
Was This Post Helpful? 0
  • +
  • -

#5 lukasz_ltt   User is offline

  • New D.I.C Head

Reputation: 18
  • View blog
  • Posts: 29
  • Joined: 12-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 11:13 AM

Use mysqli, it is very similar. Documentation you can find here.

Your query could be something like this:

UPDATE pbclickslogin SET clicks=clicks+1 WHERE id='$session_id'

This post has been edited by Dormilich: 19 October 2012 - 11:14 AM
Reason for edit:: fixed typo

Was This Post Helpful? 0
  • +
  • -

#6 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4278
  • View blog
  • Posts: 13,563
  • Joined: 08-June 10

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 11:13 AM

Quote

how would I implement that and do I not need to use a while loop as you say since my id is a primary key?

how many time would such a loop run? I think if you can answer that, the solution becomes obvious.


Quote

If I cannot use mysql_query, then what can I use that is better but gives the same effect?

either PDO or MySQLi, but not any mixtures.


Quote

The UPDATE query sounds like a good idea since I'm just updating the column

currently youíre just adding clicks, no updates. besides that I have my doubt about your DB table. you have different columns (clicked, clicks) that seem to do the same, which is a violation of database normalisation.
Was This Post Helpful? 0
  • +
  • -

#7 Hiyall   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 05-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 11:17 AM

Hmm maybe I have something wrong in my code or switched something around then? My database only contain a column named "clicks" the "clicked" is just a temporary variable or something like that lol. I'm very confused in this topic myself.

Thanks a lot for the mysqli thing, I'll make sure to make some research about that!

Damn you guys are very fast to reply and comes with so many good solutions! I'll try to bix around with it again for some hours and see if I can find a solution.

I hope you understand the DB now dormlich, it looks like this:
id, username, password, clicks, date.
Was This Post Helpful? 0
  • +
  • -

#8 Hiyall   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 05-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:02 PM

Soo I still haven't got it to work. But at least it doesn't complain now lol. It just doesn't do anything.
I have this code now:

?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<?php

require_once('connectvars.php');

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

// Connect to the database
  $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$idsession = $_SESSION['id'];

 		$result = mysql_query("UPDATE pbclickslogin SET clicks=clicks WHERE id='$idsession'") + 1;

        $query = "INSERT INTO pbclickslogin (clicks) VALUES ('$resultat')";
        mysqli_query($dbc, $query);
}
?>
<input type="submit" value="Click!" name="click" />
</form>


So I have changed some things like you said, I have both tried this:
("UPDATE pbclickslogin SET clicks=clicks WHERE id='$idsession'") + 1

and this:
("UPDATE pbclickslogin SET clicks=clicks+1 WHERE id='$idsession'")


But non of them worked. When pressing the "click" button, nothing happens. I have not changed the mysql to mysqli yet since I just want this to work before I change that. Any suggestions on how to move further on? :)
Was This Post Helpful? 0
  • +
  • -

#9 Hiyall   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 05-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:15 PM

I wanted to edit the post above but I can't seem to find an edit button lol. Well after some more research, I found out that pressing the "click" button actually makes a new row in the DB wtf? Now I have like 300 rows inside my login form because I have testet the button so many times hahah
Was This Post Helpful? 0
  • +
  • -

#10 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4278
  • View blog
  • Posts: 13,563
  • Joined: 08-June 10

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:28 PM

Quote

I found out that pressing the "click" button actually makes a new row in the DB wtf?

see line #21 in your original code.


line #14 (last code), mysql != mysqli. that canít work. besides, adding 1 to a resource is a really bad idea.
Was This Post Helpful? 0
  • +
  • -

#11 lukasz_ltt   User is offline

  • New D.I.C Head

Reputation: 18
  • View blog
  • Posts: 29
  • Joined: 12-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:29 PM

Yes its doing new row beacause each time you're using insert query.
I should not do this but to achive effect you want, try this:

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
	<?php
	/* [lukasz_ltt]:
	* I assume you have somewhere session_start(), and $_SESSION['id'] is set
	*/
	require_once('connectvars.php');

	if (isset($_POST['click']))
	{
		$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

		$idsession = $_SESSION['id'];

		/* [lukasz_ltt]:
		 * I assume you have primary key on column id
		 */
		$result = mysqli_query($dbc, "INSERT INTO pbclickslogin SET clicks=clicks WHERE id='$idsession' ON DUPLICATE KEY UPDATE clicks=clicks+1");

		if ($result)
		{
			// success
		}
		else
		{
			// failure
		}
	}
	?>
	<input type="submit" value="Click!" name="click" />
</form>



Documentation form Insert statement which I used you can find here
Was This Post Helpful? 0
  • +
  • -

#12 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4278
  • View blog
  • Posts: 13,563
  • Joined: 08-June 10

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:32 PM

@lukasz: I think you mixed up the SQL syntaxes for insert/update Ö
Was This Post Helpful? 0
  • +
  • -

#13 lukasz_ltt   User is offline

  • New D.I.C Head

Reputation: 18
  • View blog
  • Posts: 29
  • Joined: 12-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:35 PM

Nope, I did not.

Under my code there is link to documentation of syntax, which i used. (but there should bo 'for' not 'form').
INSERT ... ON DUPLICATE KEY UPDATE Syntax documentation
Was This Post Helpful? 0
  • +
  • -

#14 Hiyall   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 05-October 12

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:35 PM

Oh, if I cannot add 1 to a ressource, then how am I then supposed to add 1? Like where shall I then add it? + I still don't quite get why it adds another row?
Was This Post Helpful? 0
  • +
  • -

#15 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4278
  • View blog
  • Posts: 13,563
  • Joined: 08-June 10

Re: mysql_query problem connecting to the database

Posted 19 October 2012 - 01:36 PM

you add it inside the SQL statement.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2