[Function per field or select all data]

Hi All,

I am building an application that has users.

My questions is, when showing the user information, would it be better to have a seperate function for every item that i want to retrieve (first name, last name, email address) or should i just have a function that builds the whole data set and gets all the fields that i need in one go.

I am thinking about not duplicating my work as i will need to get the users name in other places and will need to get their email in other places.

Is it is a good idea to have a function to get each piece of data one by one or is this overkill?  I will need 10 functions to run and 10 database connections just to show basic user info.

[Pass variables from page to page]

Hi All,

I have a page that lists users - clicking a user will take you to a page where you edit the user.

I know that i can use get to pass the users ID from page A to page B but i am trying to avoid users being able to hijack the URL and navigate around like that.

I am trying to make sure that i prevent people seing what they shouldnt with permissions and sessions but i wanted to enquire if there was a better way to get around rather than get.

I have read that sessions could be an option but how would i set the session on a button click when i would need JS for the button click and PHP to set the session.

Would this be an AJAX job calling a php function.

Any suggestions on the best way would be appreciated.

[Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number]

10 hours ago, Barand said:

I wouldn't waste time on the first SELECT query.

Place a UNIQUE key constraint on the email column then

function createNewUser($pdo, $fname, $lname, $email, $password)
{
    try {
        $sql2 = "INSERT INTO user (fname, lname, email, password) VALUES (:fname, :lname, :email, :password)";
        $hashedPwd = password_hash($password, PASSWORD_BCRYPT);
        $stmt = $pdo->prepare($sql2);
        $stmt->execute([
            ':fname' => $fname,
            ':lname' => $lname,
            ':email' => $email,
            ':password' => $hashedPwd
        ]);
        return 'User Created';
    }
    catch (PDOException $e) {
        if ( str_contains($e->getMessage(), '1062 Duplicate entry')) {
            return "That email already exists";
        }
        else throw $e;
    }
}

 

OK, will give that a try.

 

Is this how i should be structuring all of my queries to interact with the database?

I have never used try / catch / throw

[Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number]

Quotes around VALUES 🤦‍♂️

[Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number]

Hi All,

I have a very simple function:

function createNewUser($pdo, $fname, $lname, $email, $password)
{
	$sql = "SELECT email from user where email = :email";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([
		':email' => $email
	]);
	$emailExists = $stmt->rowCount();

	if ($emailExists > 0) {
		return "This email address is already in use";
	}


	$sql2 = "INSERT INTO user (fname, lname, email, password) VALUES (':fname', ':lname', ':email', ':password')";
	$hashedPwd = password_hash($password, PASSWORD_BCRYPT);
	$stmt = $pdo->prepare($sql2);
	$stmt->execute([
		':fname' => $fname,
		':lname' => $lname,
		':email' => $email,
		':password' => $hashedPwd
	]);
	return 'User Created';
}

The first query is running fine, but the second is giving me

Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number (on the SQL2 line)

Can anyone shed any light on this?

[Bootstrap validation]

HI All,

I am using bootstrap and have been using its validation on some of my forms.  Mainly for "required" fields.

I have run into a bit of a tricky situation when trying to use custom validation with bootstraps validation tools.

I have a user form where i collect name, email, password and a confirm password field.

The validation works great for name and email out of the box but i have created custom functions for password validation.

I have been adding and removing the is-valid class.  What i see on the screen is what i want to see, however, when i click the submit button, the password fields validate to "all good" when my validation is suggesting that they fail.

I got this from bootstraps documentation which works for the basic stuff

// Example starter JavaScript for disabling form submissions if there are invalid fields
	(function() {
		'use strict'

		// Fetch all the forms we want to apply custom Bootstrap validation styles to
		var forms = document.querySelectorAll('.needs-validation')

		// Loop over them and prevent submission
		Array.prototype.slice.call(forms)
			.forEach(function(form) {
				form.addEventListener('submit', function(event) {
					if (!form.checkValidity()) {
						event.preventDefault()
						event.stopPropagation()
					}

					form.classList.add('was-validated')
				}, false)
			})
	})()

I have a function like this that sets on of the fileds to fail validation if the passwords do not match

	$('#password2').keyup(function() {
		$p1 = $('#password1').val()
		$p2 = $('#password2').val()
		if ($p1 != $p2) {
			$('#password2').addClass("is-invalid")
			$('#password2').removeClass("is-valid")
			$('.passwordMatchCheck').text("Passwords do not match!")
		} else {
			$('#password2').removeClass("is-invalid")
			$('#password2').addClass("is-valid")
		}

	})

I am running into a situation where my validation and bootstraps are fighting and we end up having both valid and invalid messages.

 

I am either looking for a way of omitting these fileds from bootstraps validation method or pointers on the "proper" way to do this.

 

There is nothing i can find online that suggests how this would be done.

I want to try and avoid having to write validation for all states on every form when i need something custom on one filed if can help it.

[Proper Error Handling]

This is really handy, thanks for this.

[Proper Error Handling]

HI All,

I am starting to write a new project and i wanted some advice on error handling in functions.

In my personal projects i have shamefully not really worried too much about error handling.

I am wanting to progress how i do things so i am looking at how best to handle errors.

I have written functions like this:

function getUserList($pdo)
{
    $sql = "SELECT fname from user";
    $data = $pdo->query($sql)->fetch();
    $out = '';
    if(!$data){
        return;
    }
    
    foreach ($data as $row) {
        $out .= $row['fname'];
    }
    return $out;
}

Basically starting the function, checking if there is data and if there is do something - i do tell the application what to display if there is no data rather than just return.

 

If there was an error here for ehatever reason there is nothing in place at all to handle this.  I have seen threads about try and catch but i have not used these before.

In modern programming with PDO what would be the best "catch all" suggestion that i could be implementing?

 

[Building API, PDO, Procedural]

Thanks all, i will for sure do some reading and be back if i need further help.

Thanks again.

[Getting access to data deeper in array]

Hi All i have a function

 

function showQuoteList()
{
	include 'includes/dbconn.php';

	$res = $pdo->query("WITH priceinfo as (
        SELECT qi.quote_id, SUM(discounted_price * quantity * chargable_units) as price
            from quote_items qi
            group by quote_id
                 
        )
        
        
        SELECT client.company_name as companyName
								, job.name as jobName
								, quote.id as quoteId
								, quote.name as quoteName
								, version
								, currency
								, job.internal_ref
								, kit_delivery
								, kit_return
								, quote_status_id
								, total_value 
								, quote_status.status
								, quote.date_created
								, price
								, client.id as clientId
								,client.company_name as clientName
                           from quote
                           inner join client on quote.client_id = client.id
                           inner join job on quote.job_id = job.id
                           inner join quote_status on quote.quote_status_id = quote_status.id
                           inner join priceinfo on quote.id = priceinfo.quote_id
                           order by companyName, version desc
            ");
	$data = $res->fetchAll(PDO::FETCH_ASSOC);
	if (!$data) {
		$out = "<div class='alert alert-danger'>There are no quotes to show - create one by clicking <a href='clients'>here</a></div>";
	} else {


		$results = [];
		$out = '';
		foreach ($data as $u) {
			$results[$u['companyName']][$u['jobName']][$u['quoteId']] = array_slice($u, 2);
		}

		foreach ($results as $client => $jobs) {


			$out .= "<div class='card mb-4'>";
			foreach ($jobs as $j => $items) {
				foreach ($items as $i) {
					$out .= "<div class='card-header'><a href='jobs?clientId=$i[clientId]&cN=$i[clientName]'><strong>$client</strong></a></div>";
				}
			}
              
              ..........

 

the issue that i have is that when i put the line;

 

$out .= "<div class='card-header'><a href='jobs?clientId=$i[clientId]&cN=$i[clientName]'><strong>$client</strong></a></div>";

in its current location - it is displayed multiple times as there are multiple data sets in the $items and $i.

When i move the line up inside the $jobs as $j loop, which is where it should be to display the correct information - i dont have access to the variables that i need from $i.

 

how do i get access to the variables in $i keeping that line where it should be?

 

If this is not enogh information please let me know and i will provide more context.

[Building API, PDO, Procedural]

Thanks both thats really helpful.

 

Would you suggest then, that when looking at making the API that i try and do this with OOP rather than POP.  It seems far more popular to use OOP for this.

[Building API, PDO, Procedural]

Thanks for that explanation - appreciated.

My goal here is to build another site, that is seperate from my first one that can call an API in the first to get certain information.

Eventually it will be a site that can be logged into and using the apis it will grab the information that it needs from the first site.

I am sure that there are other ways of doing this but i wanted to use it as a way of learning apis as well as building something for a reason rather than it being hypothetical.

As mentioned i have seen some tutorials that are OOP but i find them hard to follow or they are so specific to that project that i struggle to break out what i need.  I am looking to start basic - grab all client names from client table - return json.

Also i know that i will need it to be secure, so i want some sort of key exchange in there which i also have no idea about.

[Building API, PDO, Procedural]

Hi All,

I am looking to explore the world of API's and i would like to build one.

I have had a look around online and most of the examples use classes and are OOP.

I have never done OOP before and although i am sure it is something i should be looking into, i wondered if either anyone could point me in the right direction or suggest any good reading around this topic.

I am starting by just trying to send a list of Clients in the response.

Thanks in advance as always.

[Refreshing the page resending the action]

On 10/22/2023 at 3:28 PM, mac_gyver said:

the message you cited occurs when you do a 'view source' of the page in the browser. any get parameters you have in the URL should only determine what content is gotten and displayed on the page, not what post method form action is performed in the server-side code. if the last request the browser has for a URL is a html post method form submission, the form data will get resubmitted when you do a 'view source' of the page (even if you have prevented the form itself from being redisplayed on that page.)

your first post in this thread indicated that you know you could use ajax, not that you were already using it. the reply you got is based on how you would normally do this, using a html post method form submitted to server-side post method form processing code, on the same page, to prevent the browser from trying to resubmit the post method form data should that page get reloaded, a 'view source' performed on it, or the URL is browsed away from and back to, by instructing the browser to perform a get request for the exact (including any existing get parameters) same URL of the current page.

the main point of using ajax on a web page is so that you don't reload the whole page. if what you are doing means that you are going to reload the whole page anyways, you shouldn't be using ajax to do this. when you do use ajax to submit a post method form, the ajax response code needs to deal with any server-side success/failure response (validation errors, duplicate data errors, ...) that are produced and output by the server-side code.

 

 

So this should only be happening becuase i have inspector open at the same time?

 

I will do some testing around this.

 

I have noted your other comments, which i will come back to.

[Refreshing the page resending the action]

This seems to do the trick - or is this a horrible way of doing it.

if (window.history.replaceState) {
                                    window.history.replaceState(null, null, window.location.href);
                                }
                                window.location = window.location.href;

 

[Refreshing the page resending the action]

11 hours ago, Barand said:

Send a location header to same page so it reload without the posted data

 

myscript.php:

if ($_SERVER['REQUEST_METHOD']=='POST')  {
    
    // validate POST data
    
    if (no errors)  {
        
        // update database
        
        header("Location: myscript.php");
        exit;
    }
}

// build page content

 

So in one of my function (in functions.php file) that is called with ajax from other page i should put

header("Location: functions.php");

 

[Refreshing the page resending the action]

The URLs has got quite a lot of params in them.

 

Would

header("Refresh:0");

yield the same result?

 

This is currently how i am doing everything:

function applyDiscountToAllDiscountableQuoteItems($quoteId, $discountPercentage) {
                $remainingPercentage = 100 - $discountPercentage
                $.ajax({

                    type: 'post',
                    data: {
                        'ajax': 'applyDiscountToAllDiscountableQuoteItems',
                        'quoteId': $quoteId,
                        'discountPercentage': $discountPercentage,
                        'remainingPercentage': $remainingPercentage
                    },
                    success: function(resp) {
                        location.reload();
                    }
                })
            }

            function setNewItemPrice($newPrice, $itemId) {
                $.ajax({
                    type: 'post',
                    data: {
                        'ajax': 'setNewItemPrice',
                        'quoteId': $quoteId,
                        'newPrice': $newPrice,
                        'itemId': $itemId
                    },
                    success: function(resp) {
                        // location.reload();
                    }
                })
            }

 

[Refreshing the page resending the action]

My application requires the page to reload alot to trigger all of the functions that calculate price and i am now starting to see the following message alot.

To display this page, Firefox Developer Edition must send information that will repeat any action (such as a search or order confirmation) that was performed earlier.

I know that i can use AJAX to only reload sections that i have changed, but in effect i am changing so much of the page that i felt it more simple to reload the page.

Is there a simple but 'professional' way that i can prevent this message and prevent things being added to the database more than once when a reload is required?

[Union Select]

2 minutes ago, Barand said:

FetchAll() returns an array but in the second version you don't store it anywhere.

Ahh

[Union Select]

The only thing i have changed is how the statement gets its parameter.

[Union Select]

Weirdly i have another issue.

This works:

	$sql =
		"SELECT 
		qs.name as sectionName,
		i.name as itemName,
		i.id as itemId,
		i.GBP,
		i.USD,
		i.CAD,
		cb.charge_by,
		qs.display_order,
		COUNT(cp.item_id) > 0 as isConsumable
	from items i
		inner join quote_sections qs on i.section_id=qs.id
		inner join charge_by cb on i.charge_by_id = cb.id
		left join consumable_price cp ON i.id = cp.item_id
	group by i.id
	union
	SELECT 
		qs.name as sectionName,
		ci.name as itemName,
		concat('CI', ci.id) as itemId,
		ci.price as GBP,
		ci.price as USD,
		ci.price as CAD,
		cb.charge_by,
		qs.display_order,
		0 as isConsumable
	from custom_item ci
	inner join quote_sections qs on ci.section_id=qs.id
	inner join charge_by cb on ci.charge_by = cb.id
	where ci.job_id = 106
	order by display_order, itemName";

	
	$stmt = $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP);

and this does not

	$jobId = 106;

	$sql =
		"SELECT 
		qs.name as sectionName,
		i.name as itemName,
		i.id as itemId,
		i.GBP,
		i.USD,
		i.CAD,
		cb.charge_by,
		qs.display_order,
		COUNT(cp.item_id) > 0 as isConsumable
	from items i
		inner join quote_sections qs on i.section_id=qs.id
		inner join charge_by cb on i.charge_by_id = cb.id
		left join consumable_price cp ON i.id = cp.item_id
	group by i.id
	union
	SELECT 
		qs.name as sectionName,
		ci.name as itemName,
		concat('CI', ci.id) as itemId,
		ci.price as GBP,
		ci.price as USD,
		ci.price as CAD,
		cb.charge_by,
		qs.display_order,
		0 as isConsumable
	from custom_item ci
	inner join quote_sections qs on ci.section_id=qs.id
	inner join charge_by cb on ci.charge_by = cb.id
	where ci.job_id = :jobId
	order by display_order, itemName";

			
				
	$stmt = $pdo->prepare($sql);
	$stmt -> execute([":jobId"=> $jobId]);
	$stmt ->fetchAll(PDO::FETCH_GROUP);

is there something simple i am doing wrong here.

 

Neither has errors but the second does not populate the select box and the first does.

[Union Select]

They are not! And in answer to your questions, the first one.

 

All items with an indication of whether they are consumable.

[Union Select]

I think this has done what i need

select 
	i.name as itemName,
	qs.name as sectionName,
	i.id as itemId,
	i.GBP,
	i.USD,
	i.CAD,
	cb.charge_by,
    if(EXISTS(Select item_id from consumable_price where item_id = i.id),1,0) as icConsumable
from items i
inner join quote_sections qs on i.section_id=qs.id
inner join charge_by cb on i.charge_by_id = cb.id

union 
select 
	ci.name as itemName,
	qs.name as sectionName,
	concat("CI", ci.id) as itemId,
	ci.price as GBP,
	ci.price as USD,
	ci.price as CAD,
	cb.charge_by,
    0 as isConsumable
from custom_item ci
inner join quote_sections qs on ci.section_id=qs.id
inner join charge_by cb on ci.charge_by = cb.id

 

[Union Select]

I have rewritten like this

select 
i.name as itemName,
qs.name as sectionName,
i.id as itemId,
i.GBP,
i.USD,
i.CAD,
cb.charge_by
from items i
inner join quote_sections qs on i.section_id=qs.id
inner join charge_by cb on i.charge_by_id = cb.id
union 
select 
ci.name as itemName,
qs.name as sectionName,
concat("CI", ci.id) as itemId,
ci.price as GBP,
ci.price as USD,
ci.price as CAD,
cb.charge_by
from custom_item ci
inner join quote_sections qs on ci.section_id=qs.id
inner join charge_by cb on ci.charge_by = cb.id

but how do i implement your count check into this?

[Union Select]

Hi Kicken,

 

I have tied myself up here.

 

The reason for the consumable table here is to set the isConsumable flag and that is the only reason, so i could look to do your greater than 0 method.

The method i used was a hope to just get one of each item id from the consumable table.

Sql is not my strong suit so this may take some time for me to figure out.