Check database before adding

Beginners - Learning PHP
#1

Hello,

I am trying to make a system that can check the database A if the record exist before adding it the record to the database B then make a series of number to Database C. I am currently on the checking of database A if the record exist. However I always get a message record does not exist but the record actually exist. My Customer code is the one I am searching to DB A.

I do not know where is my problem here. But kindly have a look. Thanks

[code]

<?php if (isset($_POST['submit'])) { $errorMessage = ''; $con=mysqli_connect('localhost', 'root', 'LNKmis000', 'billing'); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } // include 'include/filter.php'; if($errorMessage == '' ) { mysqli_query($con,"INSERT INTO invoice_data ( cust_rec, description, part_number, fob_price, qty, total_fob, trading_price, amount ) VALUES ( '$_POST[customer]', '$_POST[description]', '$_POST[part_number]', '$_POST[fob_price]', '$_POST[qty]', '$_POST[total_fob]', '$_POST[trading_price]', '$_POST[amount]' )"); $errorMessage = 'One record added!
'; $cust_code=$_POST['customer'] ; $cust_search = mysqli_query($con,"SELECT rec_no FROM customer_info WHERE rec_no='$cust_code'"); if(mysql_num_rows($cust_search) != 0) { mysqli_query($con,"INSERT INTO invoice_number(company) VALUES ('$cust_code')"); }else { $errorMessage = $errorMessage . 'Customer record does not exist!
'; } } } mysqli_close($con); ?> 
<body>
	<div id="wrapper">
		<?php include('includes/header.php'); ?>
		<?php include('includes/nav.php'); ?>
		<div id="content">
			<h3>Invoice data entry</h3>
			<p>
				Please fill up the form below to add additional customer.
			</p>
			<p>
			<form method="post">
				<fieldset>
					<div>
					<label for="customer">Customer code</label>
					<input name="customer" type="Text" placeholder="Customer Code">
					</div>
					
					<div>
					<label for="description">Description</label>
					<input name="description" type="Text" placeholder="Description">
					</div>
					
					<div>
					<label for="part_number">Part Number</label>
					<input name="part_number" type="Text" placeholder="Part Number">
					</div>
					
					<div>
					<label for="qty">Quantity Kpcs</label>
					<input name="qty" type="Text" placeholder="Quantity Kpcs">
					</div>
					
					<div>
					<label for="trading_price">Trading Price</label>
					<input name="trading_price" type="Text" placeholder="Trading Price">
					</div>
					
					<div>
					<label for="amount">Amount</label>
					<input name="amount" type="Text" placeholder="Amount">
					</div>
					
					<?php if ($errorMessage != ''){echo $errorMessage;} ?>
					<input value="Create Invoice" name="submit" type="submit">
				</fieldset>
			</form>

			</p>
			
		</div> <!-- end #content -->
	</div> <!-- End #wrapper -->
</body>
[/code]
#2

Hello

I tried using this on MySQL query

SELECT rec_no FROM customer_info WHERE rec_no='14' and it gives me the result 14. I do not know why if it is in the PHP it does not work.

Please advise. Thanks.

#3

sorry for the trouble it seems I manage to find the problem.
The error was on the line number 54

if(mysql_num_rows($cust_search) != 0) {

It suppose to be

if(mysqli_num_rows($cust_search) != 0) {

Can someone atleast tell me why the without “i” is not working?
Thanks.

#4

I switched over to PDO for I found out it easier, but if my memory serves me correctly there’s a small glitch in mysqli that some mysqli function doesn’t work properly in all servers. Any way I found a work around for it:

[php] // Function to check if username is available:

function isUsernameAvailable($username, $mysqli) {
// The Query:

$query = "

   SELECT
       username
   FROM users
   WHERE
      username = ?
";

// These two statements run the query against your database table.

$stmt = $mysqli->prepare($query);	// Prepare the query:

/* bind parameters for markers */
$stmt->bind_param("s", $username);

/* execute query */
$stmt->execute();

/* bind result variables */
$stmt->bind_result($username);

/* fetch values */
while ($stmt->fetch()) {
    $row = $username;
}
    // The fetch() method returns an array representing the "next" row from
    // the selected results, or false if there are no more rows to fetch.

return $row; // I had to rework the check username - it works but I need to improve.
// If a row was returned, then we know a matching username was found in
// the database already and we should return a boolean value back.
}
[/php]

It’s not the most elegant, but it works. However, I find that using PDO is simpler:

[php]
// Connect to PDO database:
$db = Database::getInstance();
$pdo = $db->getConnection();

    $query = "
        SELECT
            1
        FROM users
        WHERE
            username = :username1
    ";

    $query_params = array(
        ':username1' => $this->username
    );

    // These two statements run the query against your database table.
    $stmt = $pdo->prepare($query);
    $result = $stmt->execute($query_params);

    // The fetch() method returns an array representing the "next" row from
    // the selected results, or false if there are no more rows to fetch.	   	   
    return $row = $stmt->fetch();
    // If a row was returned, then we know a matching username was found in
    // the database already and we should return a boolean value back.	   
}[/php]

With that said, maybe this helped you;however, someone who works with mysqli more might be able to help you out better.

#5

Re-reading your code again, I think you would find using the join (connecting two databases together) command in MySQl better for your problem?

#6

I think your question may have been misunderstood by other posters. Very simply, MySQL and MySQLi are two different things. MySQL is deprecated old code and should not be used. Stick to the mysqli or use PDO if you like.

#7

Hello.

Yes Kevin Rubio, it seems I was mis-understood but it is ok. I am new in PHP so I do not know some deep coding like what he is talking about. Regards to your reply that is what I was asking for.

Thanks.

Sponsor our Newsletter | Privacy Policy | Terms of Service