mysql problems

[alexho]

hello, i have been stumped for a while with mysql and handling stuff. the inserting of data to tables in the correct databases and no DATA is shown, i have tried a lot but failed miserably. I don't know what i'm doing wrong it creates the databases when i call from php, same with the tables just inserting and manipulating data is stumping me hard, any suggestions are appreciated at this point..

 

[enouwee]

any suggestions are appreciated at this point..

Provide some sample code and the database schema you're using (remove connection details before posting)?

 

[alexho]

I don't think it is my code.. but here is my standard conn file which opens db and closes at the end, this makes me think is is valid to open up the db and close it at the very end or no? I usually see people opening up the single database and closing it within doing a small action with mysql.. And i know i am connected to mysql db itself, i require this conn.php file at the very top.

standard conn:

$hostn="nafndan";
$usern="ajizadgfafdgin";
$pwn="agfsnl1afdg325x";//pw
$dbname="ajizizoc_users";
//$sqlconn = 'SELECT * FROM `user` LIMIT 0, 30 '; 
$conn = mysql_connect('localhost', 'a658jiz880izzxcvoc', 'al1fdh5031843ebdndgfx') or die(mysql_error());
global $conn;

then inserted at VERY bottom closes mysql 
===================================================
//very bottom of the site
echo "</body></html>";
mysql_close($conn);

& again thanks, im trying to get INSERT to work: here is an example for "users":
mysql_query("INSERT INTO users (name, age, location, email, user, password, avatar, signature, posts, rank, lastlogin, datejoined, ip)
VALUES('$realname', '$age', '$location', '$email', '$name_usr', '$pass_usr', '$avatar', '$signature', '0', 'recruit', '$date', '$date', '$ip'")
or die(mysql_error());
^ i try to put this inside the 'users' table! but when i try to reference and pull up the table it says i have nothing in it.. by using the while($j<$numb_membs) {
$user_o=mysql_result($result,$j,"user"); while loop.
--sorry if im messy :banghead:

 

[enouwee]

The snipped you posted there doesn't provide any useful hints to even guess where the problem could be coming from. Post actual code and the schema, not some vague description of what you think your code is doing.

Some general ideas, consider them common practice:

• If possible, don't use the mysql functions, but at least mysqli or even better: PDO-MySQL extensions. mysql is outdated and doesn't support the additional features of newer MySQL releases.
• Don't die(mysql_error()), but catch the error condition and display a generic and nice error page. You should not tell the users which DB error occurred, as it may leak sensitive information or just confuse them.
• Use bind variables instead inserting PHP variables right a query. These sare you SQL injections attacks.
• Your code is a mess and looks like straight copy-paste from the PHP documentation examples: a variable is declared global in the middle of a block, connection variables are defined, although mysql_connect() makes use of string constants. And that's only the first 6 lines of "code".

 

[alexho]

well thanks for the general ideas. I'd rather not post the entire script it would be to long, what i think is what i know for the rest, i am trying to get this to work, what else shall i show?

$query="SELECT * FROM $usersdb";
$result=mysql_query($query);
$numbrow=mysql_numrows($result);

while($j<$numbrow) {
$user_o=mysql_result($result,$j,"user");
$ava_o=mysql_result($result,$j,"avatar");
$sig_o=mysql_result($result,$j,"signature");
$loc=mysql_result($result,$j,"location");
$num_o=mysql_result($result,$j,"id");
$age_o=mysql_result($result,$j,"age");
$datej_o=mysql_result($result,$j,"datejoined");
$datel_o=mysql_result($result,$j,"lastlogin");

}
there is no result.

i am just trying to find out what could be the cause of it not writing to the table. I do not have any syntax errors, if i have any errors it would be the mysql how can i be direct if i dont know what to specify on?

 

[enouwee]

You think your script has problems with inserting data into the table, but you're posting code that actually reads from it. Assuming the mysql_result() works as intended, there is nothing really wrong with the snipped above, except it's extremely inefficient.

The documentation tells you not to use mysql_result, but the fetch functions, which are more efficient. It won't solve your problem, but at least you're not killing the DB with useless requests or transfers while debugging.

$query="SELECT * FROM $usersdb";

$rows_fetched = 0;
if (($result = mysql_query($query)) !== false)
{
  while (($row = mysql_fetch_assoc($result)) !== false)
  {
    print_r($row);
    ++$rows_fetched;
    # you'll get your fields in a [B]single[/B] call, as associative array:
    # $row['user'], $row['avatar'], ...
  }
}

 

[alexho]

yes that helps a great deal in efficiency just as other things i am aware of sql injection but that is processing input. What will solve my problem of reading/inserting data? I have already shown snippets but i don't know what you ask of.

-'code and the schema', not some vague description of what you think your code is doing.

? what schem'a, specify for the idiot.

 

[enouwee]

What will solve my problem of reading/inserting data? I have already shown snippets but i don't know what you ask of.

Either posting the code or adding debug (print "I AM HERE") statements inside your code to make sure that the INSERT instructions are really executed.

Given that MySQL isn't transaction-based (unless you explicitly tell it to), once you INSERT your data, it's immediately stored in the table, you don't have to COMMIT your changes as you have to do in i.e. Oracle. So my guess is that the query is never run because of a logic error in your PHP code.

what schem'a, specify for the idiot.

From Wikipedia:
In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables.

 

[alexho]

i think it might be the setup

 

[enouwee]

i think it might be the setup

My little finger tells me it's the script.

 

[alexho]

its not the script syntax, because it works and is valid php how can it not be if it works, so its the mysql if anything i will put everything i use snippets:

$ozza=1;
$query="SELECT * FROM users";
$result=mysql_query($query) or $ozza=0; echo "WILL install!<br>";
$rowz=mysql_num_rows($result);
if ($ozza==0 && $rowz==0) {
echo "<div id='success'>Installing system!.....$usersdb";


//users
mysql_select_db("ajizizoc_users") or die(mysql_error());
mysql_query("CREATE TABLE users(PRIMARY KEY(id), id INT NOT NULL AUTO_INCREMENT, 
name VARCHAR(30) NOT NULL, 
age VARCHAR(30) NOT NULL, 
location VARCHAR(30) NOT NULL, 
email VARCHAR(30) NOT NULL, 
user VARCHAR(30) NOT NULL, 
password VARCHAR(30) NOT NULL, 
avatar VARCHAR(30) NOT NULL, 
signature VARCHAR(30) NOT NULL, 
posts VARCHAR(30) NOT NULL, 
rank VARCHAR(30) NOT NULL, 
lastlogin VARCHAR(30) NOT NULL, 
datejoined VARCHAR(30) NOT NULL, 
ip VARCHAR(30) NOT NULL)");

echo "<p>Successfully installed $usersdb...
</div>";
}





$query="SELECT * FROM $usersdb";
$numbrow=mysql_numrows($result);

mysql_query("INSERT INTO users (name, age, location, email, user, password, avatar, signature, posts, rank, lastlogin, datejoined, ip) 
VALUES('$realname', '$age', '$location', '$email', '$name_usr', '$pass_usr', '$avatar', '$signature', '0', 'recruit', '$date', '$date', '$ip'") 
or die(mysql_error()); 

//archive root for misc file notimportant works..
$f = fopen($memb_misc,"a");
fwrite($f,"\n".$name_usr.'misc');
fclose($f);
}
//misc file create
$f = fopen($usersetting.$name_usr.'misc.txt',"w");
fwrite($f,"$name_usr should change this..");
fclose($f);
}

i have other mysql stuff where it checks for the row # from a query specific for example a users name, if its 0 it passes, more than 0 after using a constant case ucwords it will be false and die. like this

register--

other things are check user input is put into correct case so it matches that in db which is stored in same strtolower and stuff is filtered out
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

echo "<div id='success'>Checking for pass....</a></div>";

$query="SELECT * FROM users WHERE password='$pass_usr'";
$result=mysql_query($query);
$numbrow=mysql_numrows($result);
    if ($numbrow==0) {
while($j<$numbrow){
$nameok = true;
}
    }else {echo "<div id='success'>Password rejected, choose another password, not $pass_usr. And write it down.</div>"; $nameok = false; }

could it be since i am behind a router that ports are being blocked?

 

[enouwee]

$ozza=1;
$query="SELECT * FROM users";
$result=mysql_query($query) or $ozza=0; echo "WILL install!<br>";
$rowz=mysql_num_rows($result);
if ($ozza==0 && $rowz==0) {
echo "<div id='success'>Installing system!.....$usersdb";


//users
mysql_select_db("ajizizoc_users") or die(mysql_error());
mysql_query("CREATE TABLE users( [...] )");

echo "<p>Successfully installed $usersdb...
</div>";
}

You're retrieving the whole table just to see if it exists? Give it a try with 50000 entries.

Check if mysql_list_tables might fit your needs and display an error message ("Our website is under maintenance and temporarily not available", rather than creating the table).

The installation scripts be run once, then destroyed and not lie within the docroot until the end of times.

Then: why are you selecting the DB after you ran the first query and why are you ignoring the return code of the create table query. Should the create fail, everything below will through fancy error messages.

$query="SELECT * FROM $usersdb";
$numbrow=mysql_numrows($result);

What's this? Stray code from previous experiments?

mysql_query("INSERT INTO users (name, age, location, email, user, password, avatar, signature, posts, rank, lastlogin, datejoined, ip) 
VALUES('$realname', '$age', '$location', '$email', '$name_usr', '$pass_usr', '$avatar', '$signature', '0', 'recruit', '$date', '$date', '$ip'") 
or die(mysql_error());

I hope that register_globals is turned off in your PHP configuration?

What does your table really look like in the database? As you're blindly inserting that data, what happens to duplicate rows (i.e. same "name" or "email", ...)?

echo "<div id='success'>Checking for pass....</a></div>";

$query="SELECT * FROM users WHERE password='$pass_usr'";
$result=mysql_query($query);
$numbrow=mysql_numrows($result);
    if ($numbrow==0) {
while($j<$numbrow){
$nameok = true;
}
    }else {echo "<div id='success'>Password rejected, choose another password, not $pass_usr. And write it down.</div>"; $nameok = false; }

What about:
select count(1) from USERS where PASSWORD='$pass_usr'

From the security fiasco aside, that query will return either 0 or 1 rows. On zero rows, no password matched, on one row: someone guessed one of the existing passwords. You should consider encrypting your users' passwords.


So, two users can't have the same password? And all I have to know is one password in order to be able to log in?

That's cool, because I'm going to register the username again (you don't seem to be checking for it, nor does the DB enforce uniqueness) and give it the password I like. Result: by merely registering the user again, I can compromise all your accounts.

Good job.

 

[alexho]

no.. i want it to match row to row password and match a found row password to the same row for the user. If they are inputting a random user and type a password of their own it won't match, use variable for row number and compare row number user name to password row num this should work to pinpoint the RIGHT row for the right users password/name. Who says i didnt encrypt the user passwords its dumb to keep them normal when its not hard to do do same encryption to input processes as stored and compare?..

for checking duplicates names and passwords to be unique i would do the same thing as you mentioned
'select count(1) from USERS where PASSWORD='$pass_usr''

i was thinking unique passwords with users should be good why have the same passwords in a table

JOKE lol well you were right and i had a feeling it was the mysql ill figure out and see if it works since there is crapped jammed all over and messy ill have to remove it

What's this? Stray code from previous experiments?
thats the junk i wish to have inserted into the database WHEN users register important to me that it woprks i havent looked over the secuirty issues obviously because i haven't gotten that far with tables because mysql is bad in my script. encryption is important sha1 has its flaws or is it fine?

 

[enouwee]

no.. i want it to match row to row password and match a found row password to the same row for the user. If they are inputting a random user and type a password of their own it won't match, use variable for row number and compare row number user name to password row num this should work to pinpoint the RIGHT row for the right users password/name.

Well, that's not what your code is doing.


Why not just let the DB do the job:

select 1 from DUAL where exists (
  select 1 from USERS where NAME=? and PASSWORD=?
)

which are bound to $name and $pass_usr

To pin-point your "MySQL" problem, why don't you create a "test" table containing a single column and write a script, that:

1. INSERT's a value into that table
2. SELECT's that value and prints it

Just that, nothing else.

Assuming your script is correct and the insert still fails, you have a permissions problem on your database for that user. But I doubt it, as you'd be seeing a nice MySQL error because of your die().


As a side note: should $numbrow == 0, you'll end up in an infinite loop:

if ($numbrow==0) {
while($j<$numbrow){
$nameok = true;
}
    }else { [...]

 

[alexho]

thats what my code was doing before i changed it i didnt mean to do the while loop.. pinpointing would work i have tried doing that before in a simple script but it wouldnt insert or show any errors to tell me anything i dont know why... i will try it now

select 1 from USERS where NAME=? and PASSWORD=? would work well enough...... now security side of it escape string is not enough

relying on this select 1 for 1 or 0 is safe? you mentioned before something about the security part of it..