MySQL and PHP Madness! I could really use some help.

[XxDarthDexterxX]

First off, I'm brand new to this place, and I don't know which category this should go in. Seeing as I have free hosting, and I need support, I figured this would be the best place.

Me and a close friend have decided to start developing our own strategy game. We have a good few members on our forum, all helping out, and coding was going well, until today. I've recently finished a moderately long script, that randomly generates islands, and names for the islands. When I run it on my PC with WAMP, I get 100 islands inserted into the database, every time I run it.

The idea was to set up a CRON job to generate 100 islands, every two hours, but after running the script a few times to test it worked on the new server, I realized something was wrong. Instead of getting 100 islands each time I ran the script, I only ever got 1 island, and it was only the first time I ran the script. Nothing about it has changed, only the log in details, and database name. All the tables, remained with the same name, as did their headers. I'd really like help with this, as it's driving me insane. I've already torn out hair, and I'll lose more if I can't fix it.

I've looked through all the e-mails, and things you're supposed to read when you set up the account, again, looking for something to do with queries per second or minute, but I can't anything anywhere. If it's there and I'm not seeing it, could someone point it out please?

Website: http://www.spacepirates.x10.mx

 

[descalzo]

1. Wrong forum

http://x10hosting.com/forums/scripts-3rd-party-apps-programming/

2. We are not mind readers. Posting code is vital to try to get help. Make sure it it is between

code:

some
code here

or php

if( youdoitright()){
    success = True;
}

tags (click on 'go advanced' to get the buttons).

 

[Sharky]

Moved!

 

[spacepir]

Ok. It's me again, but I'm really confused as to how the forum works. Turns out we got this account when we signed up and I failed to notice. The activation e-mail didn't send so I looked through all my e-mail addresses and discovered this one existed.

Anyway, the code:

 $name = generatename(); //Generate random island name.

$islandsize = $size_id * 10; // Turn the size ID into the islands actual size.   ### NEEDS CLEANED UP ALONG WITH SIZE GENERATION ###

mysql_select_db("spacepirates");// Select the required database.

$result = mysql_query("SELECT * FROM islands WHERE island_name = '$name'"); //Check if name already exists.
$no = mysql_num_rows($result); // Check if name exists.

IF($no==1) // If it does exist, notify admin.
{
    Echo("The Island Name '$name' already exists. Island will be regenerated"); //Notify admin island exists already exists.
    $loopcount--; // Make sure the loop will run again. This ensures we're always generating 100 islands per run of the script.
}
ELSE //If it doesn't already exist:
{
    mysql_query("INSERT INTO islands (island_name, island_size, island_type, island_oreM, island_bootyM, island_techM) VALUES ('$name', '$islandsize', '$type', '$oreM', '$bootyM', '$techM' )");
    // ^^ Insert island details into the database! :D
}

I don't know why it's not working right, and I've almost given up entirely. Any help at all would be great, even if it's criticizing. I believe all criticism is constructive. :wink:

 

[Skizzerz]

You should catch the return result of mysql_query and check that if it has errors. Then you can use mysql_error() to get the error string if it indeed had an error so you can see what is wrong with it. Otherwise, I'm expecting misson or essellar will come around soonish and give you the standard lecture about not using mysql_* functions, why PDO is awesome, and possibly some other stuff.

 

[spacepir]

You should catch the return result of mysql_query and check that if it has errors. Then you can use mysql_error() to get the error string if it indeed had an error so you can see what is wrong with it. Otherwise, I'm expecting misson or essellar will come around soonish and give you the standard lecture about not using mysql_* functions, why PDO is awesome, and possibly some other stuff.

Lol. As I said, me and my friend are beginners, and don't know it all yet. If there's anything more awesome than what we're doing, I'd be happy to use it.

I have used .mysl_error(); and it's given errors, but those were promptly fixed.

 

[essellar]

Yes, one or the other of us is bound to drop by and mention that. PDO is awesome, but that's not the point, really — mysql_* will be gone soon, and if you're writing new code, there's really no sense in writing code you know will need to be rewritten. PDO is a big shift, though, so you might find it easier to adapt what you know to the mysqli_* methods. (That's the improved MySQL extension. It's not cross-DB like PDO, so you're still locked into MySQL, but it doesn't have the holes in it that ext_mysql has.)

You are, however, using SELECT * FROM ... when you only need to know if a row exists at all. There's no reason to bring back the whole row, especially if you're going to trash it anyway. SELECT island_name FROM islands WHERE island_name = $name will give you the same result (as far as your code is concerned, since you're just checking to see if there's a result at all) with less work and less memory used.

That, however, doesn't really answer the main question, which is "why is only one island being created?" Well, that's all I would expect from the code posted here. There is nothing in the code that would cause more than one island to be created—no loops or anything of that nature have been posted. It could be simply a matter of grabbing too little code when you copied and pasted. Or it could be that, in modifying the code along the way, you somehow nuked a loop that used to be working just fine and dandy, thank-you. (Unless you are working from a repository, like git, Mercurial, or SubVersion, it may be difficult to tell when the change was made and by whom, since in a flat-file space the changes simply overwrite the existing files.)

 

[spacepir]

Yes, one or the other of us is bound to drop by and mention that. PDO is awesome, but that's not the point, really — mysql_* will be gone soon, and if you're writing new code, there's really no sense in writing code you know will need to be rewritten. PDO is a big shift, though, so you might find it easier to adapt what you know to the mysqli_* methods. (That's the improved MySQL extension. It's not cross-DB like PDO, so you're still locked into MySQL, but it doesn't have the holes in it that ext_mysql has.)

You are, however, using SELECT * FROM ... when you only need to know if a row exists at all. There's no reason to bring back the whole row, especially if you're going to trash it anyway. SELECT island_name FROM islands WHERE island_name = $name will give you the same result (as far as your code is concerned, since you're just checking to see if there's a result at all) with less work and less memory used.

That, however, doesn't really answer the main question, which is "why is only one island being created?" Well, that's all I would expect from the code posted here. There is nothing in the code that would cause more than one island to be created—no loops or anything of that nature have been posted. It could be simply a matter of grabbing too little code when you copied and pasted. Or it could be that, in modifying the code along the way, you somehow nuked a loop that used to be working just fine and dandy, thank-you. (Unless you are working from a repository, like git, Mercurial, or SubVersion, it may be difficult to tell when the change was made and by whom, since in a flat-file space the changes simply overwrite the existing files.)

Ah, thanks for your reply. There is a loop, and I can assure you that it is working as it should. I'd post the entire file, but when it's 800 lines long....

I'll double check it though, to be 100% sure.

 

[misson]

Ah, thanks for your reply. There is a loop, and I can assure you that it is working as it should. I'd post the entire file, but when it's 800 lines long....

In cases like this, you can create a minimal test case.

To add to essellar's comments on PDO, one of the nicer features is support for prepared statements (which mysqli also supports, starting with PHP 5.4), which lets you keep data separate from statements instead of interpolating it. Both also support the Traversable interface for results, which means you can use a foreach loop to access result items.

foreach ($results as $result) {
    echo '<tr><td>', implode('</td><td>', $result), '</td></tr>';
}

Note the above code works when $results is the result of an SQL query, an array, or any other sort of traversable. Abstraction, it's a big win.

Combine that with a fetch mode of PDO::FETCH_CLASS and you get a completely OOP dev experience, with the consumer of the results completely unaware that the results are actually a PDOStatement (as far as the consumer is concerned, the results are a collection of objects). Then, if you later want to change the database access layer (DAL) or feed a different collection type to the consumer, you don't need to change a single statement in the consumer. Abstraction, it's a super-big win.

Follow the advice in my sig and search the forums for more.

 

[misson]

Ah, thanks for your reply. There is a loop, and I can assure you that it is working as it should. I'd post the entire file, but when it's 800 lines long....

In cases like this, it's best to create a minimal test case.

To add to essellar's comments on PDO, one of the nicer features is support for prepared statements (which mysqli also supports), which lets you keep data separate from statements instead of interpolating it. Both also support the Traversable interface for results (though mysqli only added support for it in PHP 5.4), which means you can use a foreach loop to access result items.

foreach ($results as $result) {
    echo '<tr><td>', implode('</td><td>', $result), '</td></tr>';
}

Note the above code works whether $results is the result of an SQL query, an array, or any other sort of traversable. Abstraction, it's a big win.

Combine that with a fetch mode of PDO::FETCH_CLASS and you get a completely OOP dev experience, with the consumer of the results completely unaware that the results are actually a PDOStatement (as far as the consumer is concerned, the results are a collection of objects). Then, if you later want to change the database access layer (DAL) or feed a different collection type to the consumer, you don't need to change a single statement in the consumer. Abstraction, it's a super-big win.

Follow the advice in my sig and search the forums for more.