problem related to selecting $x number of users from a database and update values of a different database table

[alexandre]

hi , i am new to programming ,in fact i am learning it simply to create a platform for the donation movement i am launching.

my problem here is that i have different databases for the different events on the website to be exact there are databases for the user acounts info and also for every different kind of events. so from here i created a script for a donation event which require me to collect the 1000 first ordered username rows in this event database, 

to then use those username to select the "winners" and update their user balance in the account info database.  

i think first i would like to know if it is possible to do so and second i really need to know if i did wrong all the way .. my head hurt . and everytime i write a question on stackoverflow they downvote it and delete it so i deleted my account and here i am. i hope to find some answers. thank you in advance if you take the time to help me.

<?php
include countdown-timer.php
$DONATIONCLASHTIME = true;
session_start();
// If the user is not logged in redirect to the login page...//
if (!isset($_SESSION['loggedin'])) {
	header('Location: index.html');
	exit;
}
if ($DONATIONCLASHTIME > 0) {
  $donationclash = true;
}
elseif ($donationclash = false) {
echo 'sorry the donation clash is not avilable at this moment';
}


$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'root';
$DATABASE_PASS = '';
$DATABASE_NAME = '';

$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if (mysqli_connect_errno()) {
	// If there is an error with the connection, stop the script and display the error.//
	exit('Failed to connect to MySQL: ' . mysqli_connect_error());
}
if (isset($_POST['participation'] and $donationclash = true)) {
	$_POST['participation'] = $donationamountinpending;
}
if ($stmt = $con->prepare("SELECT participationid, FROM donationclashdetails WHERE usernames, totaldonated = $_SESSION['name'], ?")) {

$stmt->bind_param('isi', $_SESSION['id'], $_SESSION['name'], $_POST['participation']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($participationid, $usernames, $totaldonated,);
$stmt->fetch();
$stmt->close();
}
?>
<?php
$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'root';
$DATABASE_PASS = '';
$DATABASE_NAME = '';

$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);
if (mysqli_connect_errno()) {

	exit('Failed to connect to MySQL: ' . mysqli_connect_error());
}
  if ($stmt = $con->prepare("SELECT userbalance, totaleventjoined, userlevel, userexperience, totaldonationdc, totalpifcoingained  FROM accounts WHERE username = $_SESSION['name']")) {
  // In this case we can use the account ID to get the account info.
  $stmt->bind_param('i', $_SESSION['id']);
  $stmt->execute();
  $stmt->bind_result($userbalance, $totaleventjoined, $userlevel, $userexperience, $totaldonationdc, $totalpifcoingained);
  $stmt->fetch();
  $stmt->close();
  }
  
 if ($userbalance < $donationamountinpending) {
   echo "<p>sorry you dont have enough Pifcoin to donate this amount<br>you can buy more by visiting the <a href='shoppingcart.php'>shop.</a></p>";
   echo $donationamountinpending, echo $userbalance;
 }
 else if ($userbalance >= $donationamountinpending) {
   
   $usernames = $_SESSION['name'];
   $donationamountinpending = $donationamount;
   $userbalance = $userbalance - $donationamount;
   $totaldonationdc = $totaldonationdc + $donationamount;
  $userexperience = $userexperience + $donationamout;
  $userlevel = $userlevel + ($userexperience + 100) / 10;
  $totaleventjoined = $totaleventjoined++;

  echo "you successfully participated to the Donation clash.<br>You can keep track of how much you donated for the week in your profile.";
  echo "or go take a look at the donation ranking page.";
}

if (!isset($donationamount <= 0)) {
  $totalpifcoincollected = $totalpifcoincollected + $donationamount;
  $totaldonated = $totaldonated + $donationamount;
}

mysqli_select_db($con, 'donationclash');
//this will be to do a ranking of the donations//
$sql = 'SELECT usernames, FROM donationclashdetails ORDER BY totaldonated DESC';
$result = mysqli_query($con, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo ' - ' . $row['usernames'] . $row['totaldonated'] . ' - ' .'<br>';
    }
} else {
    echo 'No records found!';
}
$con->close();
if ($donationclash = false) {
$donationclashcompleted = true;
echo 'The donation clash of this week is now completed'
}
mysqli_select_db($con, 'donationclash');

$sql = 'SELECT sum(totalpifcoincollected), FROM donationclashdetails ';
$result = mysqli_query($con, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
       echo "the total collected this week for the donation clash:"$row['totalpifcoincollected'];
     }
   
       $con->close();
     }
     mysqli_select_db($con, 'donationclash');
     //this is for selecting the 1000 winners of the week//
     $sql = 'SELECT usernames, totaldonated, FROM donationclashdetails LIMIT(1000) ORDER BY totaldonated DESC';
     $result = mysqli_query($con, $sql);
     if (mysqli_num_rows($result) > 0) {
         while($winnerrow = mysqli_fetch_assoc($result)) {
             echo ' - ' . $winnerrow['usernames'] . $winnerrow['totaldonated'] . ' - ' .'<br>';
         }
     } 
   if ($donationclashcompleted = true) {
     /*here i retain 35% of the donations of the week in order to 
     distribute it between multiple voted foundations during the week*/
    $prizeamount = ($winnerrow['totaldonated'] -35%) / 1000;
    $winners =  $winnerrow['usernames'];
   }
     if isset($winners) {
       /* !!! i am lost !! also it could all be errors everywhere since it is mostly a theorical script not the finish product at all.
       if you have any advices , feel free to share your opinion.. and no  i didnt test anything in this code i was so scared that it breaks my pc.. yet it was all from head but i am curious to see at wich point i was wrong. */ 
   }
     else {
         echo 'No records found!';
     }
?>

 

Edited September 1, 2022 by alexandre

[Barand]

The first thing to try is change

$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);

to

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);

so errors in your queries get reported

[alexandre]

Thank you , for the advice. I will do so , I don’t even understand that I didn’t see that was missing. 

[Barand]

From your code It looks like there are three people working on the script.

One writing the queries (who has no regard for query syntax rules), one binding the parameters and a third binding the results - and the three of them never communicate with the other two.

[alexandre]

Sadly I am working alone on this but I effectively don’t know a lot about syntax rules. 
honestly I was writing in my logic to form my idea first and was thinking to make the adjustments needed once I would know better but I think I must have messed everything around. You have to assume here that another file contain a label for participation amount and a submit button where the form uses post method, I don’t know if it matters but if you think I should redo the whole code from scratch I might have to. Oh and if it looks this way it might be because I helped myself with many tutorials and code examples out there and it can sometimes get confusing since almost nobody uses the same syntax …

Edited September 1, 2022 by alexandre

[Barand]

These are what prompted my remarks

Example 1

$stmt = $con->prepare("SELECT participationid, FROM donationclashdetails WHERE usernames, totaldonated = $_SESSION['name'], ?")
$stmt->bind_param('isi', $_SESSION['id'], $_SESSION['name'], $_POST['participation']);
$stmt->bind_result($participationid, $usernames, $totaldonated,);

    Query: Syntax errors
           totaldonated = name (really???)
           
    Params binding: 3 parameters but only 1 "?" placeholder (wrongly placed) in the query

    Result binding: 3 results bound but only 1 column selected

Example 2

$stmt = $con->prepare("SELECT userbalance, totaleventjoined, userlevel, userexperience, totaldonationdc, totalpifcoingained  
        FROM accounts WHERE username = $_SESSION['name']")
$stmt->bind_param('i', $_SESSION['id']);

    Query: Syntax error plus WHERE clause is using a name 
           
    Params binding: parameter is an id (int) with no placeholders in the query

 

[alexandre]

Now that you note it dang you are right .. I don’t know what I was doing ..

[alexandre]

And for the total donated equal the session name is that I put usernames first and then total donated  so the amount donated is unknow and set by the user and also bind the usernames to the session user names . Since the event have to list the usernames but those are in a different database with user account info  and I didn’t really know how to do this yet.

Edited September 1, 2022 by alexandre

[alexandre]

Should I add more of my  files like the authenticate.php for register and login and everything maybe it would help , because I have my website done and registering and login working fine, I wanted to attack the most complicated of the features I wanted to add first. Let me know if you need to see more. 

[Barand]

1 hour ago, alexandre said:

Should I add more of my  files

No thanks - no-one will read them. We only want to see what's relevant to the problem.

[gizmola]

2 things that will be of value for you:

• PHP has include/require.  It allows you to put something in a script and include it as if you had typed it in.  What jumps out immediately is that you have database credentials. When you change them you don't want to have to change every script in your system.  Put them in a script and require_once them instead.

Here is a pretty typical PHP application directory structure you can use. 

• Your webserver document root should be set to the /path/to/your_project/public directory.
  • This allows you to keep some files outside of the webroot, so hackers can not attempt to execute them directly

your_project/
├─ include/
│  ├─ config.php
│  ├─ utility_functions.php
├─ public/
│  ├─ css/
│  ├─ js/
│  ├─ index.php

 

In the /your_project/include/config.php file you put your database setting variables.

$DATABASE_HOST = 'localhost';
$DATABASE_USER = 'root';
$DATABASE_PASS = '';
$DATABASE_NAME = '';

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$con = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);

if (mysqli_connect_errno()) {
	// If there is an error with the connection, stop the script and display the error.//
	exit('Failed to connect to MySQL: ' . mysqli_connect_error());
}

 

When a script needs a mysqli connection to use you require_once config.php.

In index.php for example:

<?php 
// index.php

require_once('../include/config.php');

// Rest of code here, can already use $con

 

The other thing I would suggest is trying to use functions for individual tasks.  If you can put code into a function,  it will be possible to re-use it in multiple scripts. 

By changing to a typical structure like the one I describe, you also open yourself up to the possibility of using component libraries in your application, as well as autoloading via the composer dependency management tool. 

This does require some basic understanding of PHP's object oriented programming syntax, at least so far as being able to instantiate an object with "new className()". 

This is probably the best thing as a novice developer you could do at this point, other than to port your application to a basic framework like Cakephp. 

Seeing the road you are going down, and in recognition of your neophyte status, I would highly advise taking a look at using Cake, because it is very simple and easy to understand, and will give you the basic MVC structure a web application should have.  It's easy to see for most of us, that you are already making huge mistakes, and writing spaghetti that will be buggy, insecure, impossible to maintain, and involving tremendous reinvention of the wheel.    CakePHP can relieve you of a lot of that, and has a simplicity to it that is well suited for relative beginners.

I tried to find you a free online course to help you learn CakePHP, and this one looked decent, and the person teaching how to use it is well spoken and knowledgable.

 

If you do use CakePHP I think the equivalent for the public directory/webroot is a directory named /webroot.  Frameworks like CakePHP have code generation tools and will typically offer a way to initialize your project via composer.  This is covered, I believe in the tutorial series I posted about.

Here is the main CakePHP site:  https://cakephp.org/

Basically what you will do is make a new CakePHP project on your workstation.  You can then "port" ie. move relevant code you want to preserve into files in your CakePHP project.

CakePHP gives you structure you don't have now, like controllers/routing, models (that will match your database tables) and views (templates).  

Once you get these simple concepts, the framework will save you incredible amounts of time, in providing you built in classes and structure that let you concentrate on your actual logic and functionality.

 

[alexandre]

thank you i will definitely take a look at this cakePHP and yes it is the first php code i was ever writing or at least partially writing. i might have to continue learning i just found it hard to find a good php course that isnt deprecated. i think i followed at least 3 or 4 of them to end on something that wasnt working anymore and i couldnt find a way to go around this deprecations by myself so it got a bit complicated. its relatively easy to integrate premade features compared to making them from scratch. anyways thank you for your advices i will come back if i ever get better.