FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Mysql 101 question





m-productions
I am working on a flash site, and ... learning php as i go, (and its comeing along rather well, a few problems here and there but ive been able to solve them) however, I still dont know much php. heres what I would like to do.

in my table members_tbl i want to find what number is under the collume "character" so and then give flash the number, the part i dont know how to do is...how to set the number in the table to a php varibale....for example this is what i want to do.



Code:


<?php
session_start();
$user = $_POST['username'];

//mysqldetails
require_once("dbDetails.php");

$SQL = "SELECT * FROM members_tbl WHERE username ='".$user."' AND character >= 1";

$rs = mysql_query($SQL,$conn);
$numRows = mysql_num_rows($rs);



if($numRows > 0){
$character = ~_~!!!! whatever number character is = to !!!!~_~
   echo '&character='.$character.;
}else{
   echo '&character=none';
}

?>


so basicly the
$character = ~_~!!!! whatever number character is = to !!!!~_~
is what im trying to get. soooo if its greater or = to 1, then set it to the variable $character....thats all i need, like i said, mysql 101 .... however i just dont know it =)-
hexkid
You need the mysql_result() or one of the mysql_fetch_*() functions.

Code:
<?php
$sql = "select character from ...";
$rs = mysql_query($sql) or die('error');
if (mysql_num_rows($rs)) {
  $character = mysql_result($rs, 0, 0);
}
?>
Code:
<?php
$sql = "select character from ...";
$rs = mysql_query($sql) or die('error');
while ($row = mysql_fetch_row($rs)) {
  $character = $row[0];
}
?>
Code:
<?php
$sql = "select character from ...";
$rs = mysql_query($sql) or die('error');
while ($row = mysql_fetch_assoc($rs)) {
  $character = $row['character'];
}
?>
m-productions
First off, thank you hexkid, you are seriously one big big help!

anyway, I posted this because i do not know how to do it, and when i get to this part in my flash file (which is very soon) i would like to be ready to give it a go. So i cannot test this yet, and i know you have noway of knowing if this will work, however, this is the script i think im going to use (using what you just gave me) can you tell me if the script looks error free.


}

Code:

<?php
session_start();
$user = $_POST['username'];

//mysqldetails
require_once("dbDetails.php");

$SQL = "SELECT * FROM members_tbl WHERE username ='".$user."' AND character >= 1";

$rs = mysql_query($SQL,$conn);
$numRows = mysql_num_rows($rs);



if($numRows > 0){
while ($row = mysql_fetch_assoc($rs)) {
  $character = $row['character'];
}
   echo '&character='.$character.;
}else{
   echo '&character=none';
}

?>


so lets say my tables like

|id|username|character|
|3|~~mol~~ | ~~7~~~|

when that script is run, it would set $character to 7 ,correct?
Rhysige
That script looks right yeah... vey nice Razz
m-productions
im guessing there is an error in it somewhere, beacuse its not getting to the end. It dosnt seem to echo the &character=none to flash, so if it dosnt even get down to the echos, im guessing theres an error, ...so to better test the error, can you tell me how i can go to

http://www.m-productions.frih.net/char.php and from there, mainly input the varaibles instead of letting flash do it (like have a text field or somthing) i tryed adding a text filed and sumbit button, but it dosnt work. and to think i got the register and login to work, and now to get stuck on this =(
MrBlueSky
In this line:

Code:

 echo '&character='.$character.;


you have a . (dot) where there should not be one. Change it to:

Code:

echo '&character='.$character;
hexkid
Code:
<?php
session_start();
$user = $_POST['username'];

//mysqldetails
require_once("dbDetails.php");

###
$safe_user = mysql_real_escape_string($user);

###
# $SQL = "SELECT * FROM members_tbl WHERE username ='".$user."' AND character >= 1";
$SQL = "SELECT * FROM members_tbl WHERE username ='$safe_user' AND character >= 1";

$rs = mysql_query($SQL,$conn);

###
if (!$rs) {
  // query failed. For simplicity sake, print the error message and exit
  die('Query failed. The reason for failure was: ' . mysql_error());
}

$numRows = mysql_num_rows($rs);

if($numRows > 0){

### this while() will loop through all the rows in the database.
### if you're only interested in one row, you probably do not
### need the while(). Use mysql_result() instead
###
### Also, if you're only interested in the column `character`,
### why do you fetch them all from the database?
### A simple "select character from members_tbl where ..." would be enough
#while ($row = mysql_fetch_assoc($rs)) {
#  $character = $row['character'];
#}
   define('CHARACTER_INDEX', 0); // if you "select *" specify the correct index here
   $character = mysql_result($rs, 0, CHARACTER_INDEX);

### & is used to start character entities in HTML.
### it probably isn't the reason your code is failing,
### but I prefer to expand all such entities to their value

##############
### AHA!!! ###
##############
### there's a dot too many here

#   echo '&character='.$character.; // extra dot before semicolon
   echo '&amp;character='.$character;
}else{
   echo '&amp;character=none';
}

?>
m-productions
omg thank god for the
die('Query failed. The reason for failure was: ' . mysql_error());
you added, it gave me this

Query failed. The reason for failure was: 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 '>= 1' at line 1

i looked in my php book and that is a usable syntax....


and on another note, The reason i use SELECT * FROM instead of SELECT character FROM is because im a PHP noob, I have just learned and tought myself this, and didnt know you could do that (the book just said the * stands for the wild card and I didnt know what it was a wild card for lol) so anyway... what should i do do fix this syntax error.... ive tryed fooling around with it, but no luck
MrBlueSky
Question: is the character column in your table set to VARCHAR (or some other text-type)? If so than your query won't work because the >= operator only works on columns of numeric types.
m-productions
yes, character is set to int(11) to be exact. int does stand for Integer correct?
MrBlueSky
Could change the line

Code:

die('Query failed. The reason for failure was: ' . mysql_error());


to

Code:

die('Query failed. The reason for failure was: ' . mysql_error().'<BR>'.$SQL);


then run the script again and copy the complete message here?

(PS. yes INT stands for Integer)
m-productions
&character=ohnoQuery failed. The reason for failure was: 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 '>= 1' at line 1
SELECT * FROM members_tbl WHERE username ='' AND character >= 1

thats what pops up (note there is no variable there becuase none was given (i went right to http://www.m-productions.frih.net/char.php) give ne a second and ill go give it a var in flash

ohnoQuery failed. The reason for failure was: 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 '>= 1' at line 1<BR>SELECT * FROM members_tbl WHERE username ='mol' AND character >= 1

there it is with the var, looks perfect to me, but im not a php pro =)-
MrBlueSky
Change

Code:

$SQL = "SELECT * FROM members_tbl WHERE username ='$safe_user' AND character >= 1";


to:

Code:

$SQL = "SELECT * FROM members_tbl WHERE username ='$safe_user' AND 'character' >= 1";
m-productions
good news and bad news....

First the good, I have done what you told me and the syntax error is no more ^^ perfect. Then i went into flash, and log in as mol, (in the database the username mol has character set to 0) and to great success the php file echoed character=none back to me, perfect!!!

Bad news, I then loged on with molster, molster has a character set to 6 in the database, however, it came back as none as well, so i then told it to tell me what $character was set to in the php file, and it was 100% blank. nothing in it at all =( .................
MrBlueSky
I think you have to change

Code:

   define('CHARACTER_INDEX', 0); // if you "select *" specify the correct index here
   $character = mysql_result($rs, 0, CHARACTER_INDEX);


to

Code:

   define('CHARACTER_INDEX', 1); // if you "select *" specify the correct index here
   $character = mysql_result($rs, 0, CHARACTER_INDEX);


because in a previous post I saw you stored the username in the second column
m-productions
Yes it is, however, i still get the same problem, my table looks like this

|id|username|gold|character|email|password|

just in case that shows any other errors you may see


(and just so you know, the second we get this to work, your getting some $frih for sure!!! you and hexkid are like the best!)

Code:

  $character = mysql_result($rs, 0, CHARACTER_INDEX);


would the 0 in that need to be changed as well?


Edit: nvm I just read up on mysql_result() and the 0 in that is for the rows....so nvm... i dont know what to do then =(
MrBlueSky
What does the login-form look like? Can you post it here? Or is it Flash?
m-productions
its all flash based, the login works fine, its actully calling for "character" on another frame with the following script.

Code:

stop();
var lvSend:LoadVars = new LoadVars();
var lvReceive:LoadVars = new LoadVars();


   lvSend.username = _root.main.account;
   
   lvSend.sendAndLoad("char.php", lvReceive, "POST");


lvReceive.onLoad = function(success:Boolean) {
   if (success) {
      if (this.character == "none") {
         test.text = this.character;
         
      } else {
         test.text = this.character;
         
      }
   } else {
      test.text = error
      
   }
};

The code here is perfect 100% (im no noob when it comes to my actionscript =)- ) when i echo '&character' that just echoed the variable this.character that you see in the script. and I know it works, cuase its showing that is =s none. and it only =s none if character is 0
=(.


and if you still need to see the login script its this.

Code:

var lvSend:LoadVars = new LoadVars();
var lvReceive:LoadVars = new LoadVars();



reg.onRelease = function() {
   reg.gotoAndStop(2);
   account = username.text;
   lvSend.email = username.text;
   lvSend.password = password.text;
   lvSend.sendAndLoad("logincheck.php", lvReceive, "POST");
};

lvReceive.onLoad = function(success:Boolean) {
   if (success) {
      if (this.login == "success") {
         _root.main.account = account;
         _root.gotoAndStop(3);
         reg.gotoAndStop(1);
         error.gotoAndStop(1);
         
      } else {
         error.gotoAndStop(5);
         reg.gotoAndStop(1);
         
      }
   } else {
      error.gotoAndStop(4);
      reg.gotoAndStop(1);
      
   }
};


however I dont think seeing this will help you at all because its all flash side actionscript.... lol but just incase there you go.

edit: ummm i have a question... (once again dont forget im a php noob)

Code:

SELECT * FROM members_tbl WHERE username ='mol' AND 'character' >= 1


here it says
"username ='mol'"

does that mean its looking for the 'mol' in that collume or mol cuase if it really is looking for 'mol' it isnt going to find it cuase it dosnt have ' on it lol...... (and i do know text within the ' ' means string... but i duno.... i feel so helpless cuase i dont know anything =( its a horrible feeling lol
MrBlueSky
I want to try one more thing. Change

Code:

<?php
session_start();
$user = $_POST['username'];


in

Code:

<?php
session_start();
$user = $_POST['username'];
print_r($_POST);


and put the output here, if you will.
MrBlueSky
m-productions wrote:
here it says
"username ='mol'"

does that mean its looking for the 'mol' in that collume or mol cuase if it really is looking for 'mol' it isnt going to find it cuase it dosnt have '


No, that statement is oke Smile
m-productions
ok, when i login using flash, i once again only get none

however, if i go to the .php file itself (meaning $username will be undefined) i get the following

Array ( ) &character=none


your more than welcome to login to the flashsite itself if you like (i dont know how much it would help)
http://www.m-productions.frih.net

accountname: molster
password: molster

that account has character set to 6

and then you can make an account or use
accountname: mol
password: mol
to have an account where it is set to 0 in the database ....
but I dont think any of that will help you at all.... =/
knowing me, its gana be somthing so dumb.... lol
MrBlueSky
m-productions wrote:
ok, when i login using flash, i once again only get none

however, if i go to the .php file itself (meaning $username will be undefined) i get the following

Array ( ) &character=none


But wat is the complete output of the php file if you login from flash?
m-productions
none

thats the full output.
flash is only going to show whatever the variable character is = to. and in order to set the variable character to somthing, in php i must use

Code:

echo '&character=whatever i want character in flash to equale' ;

or
Code:

die (&character=whatever i want character in flash to equale);
MrBlueSky
Oke, I have to leave now. It's 5:10am here, so I have to get some sleep Rolling Eyes
I will be back tomorrow, and we get this thing to work, no matter what Very Happy
m-productions
alright sleep well then lol, and thank you so much for the dedication to this, i mean, i was so happy i learned all this php, and everyting was going so good, and it came to this part, and i thought id be able to do it with no problem, and instead im totally helpless with it =(


i added the following code to one page
Code:

<!-- in HTML mode -->
<form action="char2.php" method="post">
<input type="text" name="username" value="<?php echo $variable; ?>"><br>
<input type="submit">
</form>


and then i got the following output on the next page

Code:


Array ( [username] => molster ) &character=none
MrBlueSky
Can you post a dump of the table 'members_tbl'? (using 'Export' in phpMyAdmin)
m-productions
Code:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 27, 2007 at 10:29 AM
-- Server version: 4.0.23
-- PHP Version: 4.4.4
--
-- Database: `mproduct_data`
--

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

--
-- Table structure for table `members_tbl`
--

CREATE TABLE `members_tbl` (
  `ID` int(11) NOT NULL auto_increment,
  `username` varchar(255) default NULL,
  `gold` int(11) default NULL,
  `character` int(11) NOT NULL default '0',
  `email` varchar(255) default NULL,
  `password` varchar(255) default NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

--
-- Dumping data for table `members_tbl`
--

INSERT INTO `members_tbl` VALUES (1, 'molster', NULL, 6, 'molsterssomail.com', 'molster');
INSERT INTO `members_tbl` VALUES (2, 'test', NULL, 0, 'test', 'test');
INSERT INTO `members_tbl` VALUES (3, 'headie', NULL, 0, 'durrr@ugn.com', '10201989');
INSERT INTO `members_tbl` VALUES (4, 'test1011', NULL, 0, 'molsterssomail.com', 'test');
INSERT INTO `members_tbl` VALUES (5, 'shock', NULL, 0, 'shockmonkey23ahoo.com', 'shock');
INSERT INTO `members_tbl` VALUES (6, 'mol', NULL, 0, 'mol', 'mol');
INSERT INTO `members_tbl` VALUES (7, 'mol2', NULL, 0, 'whateevr@yashoo.coskd', 'mol');
MrBlueSky
Damnit. Your query looks oke given that table and still it returns 0 rows in the result. I have no idea what's going wrong Shocked
MrBlueSky
LOL Laughing

I think I found it:

Change

Code:

$SQL = "SELECT * FROM members_tbl WHERE username ='$safe_user' AND 'character' >= 1";


Code:

$SQL = "SELECT * FROM members_tbl WHERE username ='$safe_user' AND `character` >= 1";


(Backticks instead of singles quotes around character)
hexkid
I'm back Smile


What's your current SQL command?
Quote:
SELECT * FROM members_tbl WHERE username ='mol' AND 'character' >= 1


What's your PHP command to fetch the data?
Quote:
$character = mysql_result($rs, 0, CHARACTER_INDEX);
m-productions
well first the good news, it is actully setting $character to somthing now, however, its not setting it to 6 (the value under character) its setting it to "molster" (the account name ... o.0) *sigh* well its almost perfect... lol


and hexkid, my current php code is as follows.

Code:

<?php
session_start();
$user = $_POST['username'];
print_r($_POST);

//mysqldetails
require_once("dbDetails.php");

###
$safe_user = mysql_real_escape_string($user);

###
# $SQL = "SELECT * FROM members_tbl WHERE username ='".$user."' AND character >= 1";
$SQL = "SELECT * FROM members_tbl WHERE username ='$safe_user' AND `character` >= 1";

$rs = mysql_query($SQL,$conn);

###
if (!$rs) {
echo '&character=ohno';
die('Query failed. The reason for failure was: ' . mysql_error().'<BR>'.$SQL);

}

$numRows = mysql_num_rows($rs);

if($numRows > 0){

### this while() will loop through all the rows in the database.
### if you're only interested in one row, you probably do not
### need the while(). Use mysql_result() instead
###
### Also, if you're only interested in the column `character`,
### why do you fetch them all from the database?
### A simple "select character from members_tbl where ..." would be enough
#while ($row = mysql_fetch_assoc($rs)) {
# $character = $row['character'];
#}
define('CHARACTER_INDEX', 1); // if you "select *" specify the correct index here
$character = mysql_result($rs, 0, CHARACTER_INDEX);


### & is used to start character entities in HTML.
### it probably isn't the reason your code is failing,
### but I prefer to expand all such entities to their value

##############
### AHA!!! ###
##############
### there's a dot too many here

# echo '&character='.$character.; // extra dot before semicolon
echo '&character='.$character;
}else{
echo '&character=none';
}


?>
MrBlueSky
Set

Code:

define('CHARACTER_INDEX', 1);


to

Code:

define('CHARACTER_INDEX', 3);
m-productions
and its uploading as i type this .....

its now uploaded...... testing....


and and..............AND IT WORKS!!!!!!! OH YEAH!!!!!! ( took about 24 post in a thread that i thought would only take one...) BUT IT WORKS!!! thank you both so much.... I think thats about the last of the php help ill need for a bit....well....I might need help with one more thing, but its just an edit of this script.... if i need help with it ill make a thread on it, but until then, youv both been beyond helpfull, thank you so much ill make sure to add both of you as "secret characters" on my site (tho you have no idea what i mean by that... when my sites done youll see!! lol) anyway, yep thank you both so much!
m-productions
frist i want to say sorry for the double post, I wanted to make sure they knew there was a new post



anyhow, Ive worked on 2 new scripts... one that changes and checks gold, and one that changes and checks character (and i did them all by myself, im so proud of myself LOL) the gold one i got done error free, however, the character one...got some errors.....

Code:

<?php
//--------------------MYSQL DETAILS ---------------------------------------//

//mysql details
require_once("dbDetails.php");


$username = $_POST['username'];
$character = $_POST['character'];

$insertSQL = "update members_tbl set `character` = '$character' where username = '$username'";

$rs = mysql_query($insertSQL,$conn);
echo 'this is:' .$insertSQL;

if($rs){

   echo '&result=success&';
}else{
  echo '&result=failure&';
}


?>


the script above works, because i added the ` ` to character (which i learned to do thnx to you ^^ ).... and it wont work without the ` `, could you explain to me why this is.... its not "important" that i know why it is... i just really want to know why lol.
MrBlueSky
m-productions wrote:


the script above works, because i added the ` ` to character (which i learned to do thnx to you ^^ ).... and it wont work without the ` `, could you explain to me why this is.... its not "important" that i know why it is... i just really want to know why lol.


character is a reserved word in MySQL queries. So you have to use backticks (`) around it. At first I thought single quotes (') would do. Only after importing your table dump in a database and trying the query I found out that single quotes didn't work, and that it had to be backticks Smile
Related topics
mysql connection question
php admin and mysql admin console
Question about MySQL!
PHP, MySQL...
PHP Book (Php and Mysql for Dynamic Web Sites)
[Question] Visual Basic C++, PHP, mySQL language guides...
SQL Server 2000 and MYSQL, pls help me.
Mysql database problem
PHPBB MYSQL Database Question.
PHP MySQL Commands Question
how to install phpbb forum?
MySQL and PHP Question
MySQL JOIN Question
learning MySQL
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.