Posts by cereal

Could you paste the code here?

Hi, can you show us the insert script?

Hi, in this case it would be bindColumn() instead of bind_result() as the OP is using PDO, not MySQLi:

• http://php.net/manual/en/pdostatement.bindcolumn.php

// edit
I added the PDO tag to the thread, to avoid confusion, bye! :)

Hi, I would probably start from this link:

• http://www.oracle.com/technetwork/database/features/plsql/index.html

@Awais_2 hello, please: follow the community rules, open a new thread and share the code that is giving you the issue.

Hi, the article you're reading seems a bit outdated and it does not support the Laravel Auth system, in 5.2 now you can specify you're own guards, follow:

• https://laravel.com/docs/5.2/authentication#authenticating-users
• http://blog.sarav.co/multiple-authentication-in-laravel-5-2/

It should work fine.

Hi,

once you have selected a main category, just add one or more tags, as you did with forums, for the database flavour you wish to write about. That helps people to filter what they want to read, but the thread will still be visible in the main category, i.e. in databases.

To see the other sections click on the blue burger icon on the up-right side of the page, it will open a dropdown menu.

In the community center you can read about the tag system and the new categories.

Hello, have you tried with a left join? If table 1 is reference then try this example for MySQL:

SELECT `r`.`no`, `r`.`referenceno`, `r`.`date`, `m`.`controlno` FROM `reference` AS `r` LEFT JOIN `marine` AS `m` ON `m`.`referenceno` = `r`.`referenceno` AND `r`.`marine` = 'y' ORDER BY `r`.`no`;

Live example here: http://sqlfiddle.com/#!9/b2fca/1

Knowing the data model relationship (one to many, one to one, ...) and the database (MSSQL, MySQL, PostgreSQL, ...) could help.

Hi,

at the moment you're using the $username variable directly inside the query and without quotes:

where username = $username

change it to the placeholder:

where username = :username

as is set by the bindValue() method.

Also, if not used, remove the :roleid bindValue() as that will produce:

'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'

i.e. a fatal error that will stop the script.

If this still does not solve, then enable the PDO exceptions and the PHP error logging and post back with detailed errors.

@hielo hi! On MySQL you can use the SET clause in the INSERT statement, both syntaxes are supported, see:

• http://dev.mysql.com/doc/refman/5.7/en/insert.html

To get the difference between two dates you can use datediff():

• http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff

To get the difference without weekends you could also do:

drop function if exists diffdate;
delimiter //
CREATE FUNCTION diffdate(date2 DATETIME, date1 DATETIME)
RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE dt1 DATETIME;
DECLARE i INT;
DECLARE wd INT UNSIGNED;
SET i = 0;
SET wd = DAYOFWEEK(date1);
SET dt1 = date1;

IF DATEDIFF(date2, dt1) > 0 THEN
    WHILE DATEDIFF(date2, dt1) > 0 DO
        SET dt1:=DATE_ADD(dt1, INTERVAL 1 DAY);
        SET wd:=DAYOFWEEK(dt1);
        CASE WHEN wd in(2,3,4,5,6) THEN SET i:=i+1;
        ELSE SET i:=i;
        END CASE;
    END WHILE;
ELSEIF DATEDIFF(date2, dt1) < 0 THEN
    WHILE DATEDIFF(date2, dt1) < 0 DO
        SET dt1:=DATE_SUB(dt1, INTERVAL 1 DAY);
        SET wd:=DAYOFWEEK(dt1);
        CASE WHEN wd in(2,3,4,5,6) THEN SET i:=i-1;
        ELSE SET i:=i;
        END CASE;
    END WHILE;
END IF;

RETURN i;
END//
delimiter ;

then you can play your queries like these:

> select diffdate(now(), '2016-05-31 00:00:00') as 'without weekends', datediff(now(), '2016-05-31 00:00:00') as 'with weekends';
+------------------+---------------+
| without weekends | with weekends |
+------------------+---------------+
|               -2 |            -4 |
+------------------+---------------+
1 row in set (0.00 sec)

> select diffdate('2016-05-31 00:00:00', now()) as 'without weekends', datediff('2016-05-31 00:00:00', now()) as 'with weekends';
+------------------+---------------+
| without weekends | with weekends |
+------------------+---------------+
|                2 |             4 |
+------------------+---------------+
1 row in set (0.00 sec)

> select diffdate(now(), now()) as 'without weekends', datediff(now(), now()) as 'with weekends';
+------------------+---------------+
| without weekends | with weekends |
+------------------+---------------+
|                0 |             0 |
+------------------+---------------+
1 row in set (0.00 …

Hi, have you tried http_build_query()?

• http://php.net/manual/en/function.http-build-query.php

What happens if they choose the same password? Usernames should be unique, if you cannot then make something else unique, like email addresses and force the sign in through the unique column. By fixing this, you solve the issue.

@monica2016

Hello, please open a new thread. I haven't downvoted your post but you should follow our community rules:

• https://www.daniweb.com/community/rules

Hi, you can try with the BETWEEN ... AND construct in the WHERE clause:

• http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between

Example with date_column:

"SELECT `MatchTitle`, COUNT(`MatchTitle`) AS `mostPlayed` FROM `matches` WHERE `date_column` BETWEEN "2016-05-24 23:59:00" AND "2016-05-25 00:00:00" GROUP BY `MatchTitle` ORDER BY `mostPlayed` DESC LIMIT 1"

This will give 1 minute range. Just change date ranges according to your needs.

Use intval() as suggested or $id = (int) $_GET['id'];, see:

• http://php.net/manual/en/language.types.type-juggling.php

Or in case of validation use filter_input():

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);

Bye!

Little note: consider that the query() method will return an iterable object from the Result class:

• http://php.net/manual/en/class.mysqli-result.php

So, if you want to get the next row you have to move the internal pointer, through a loop or through data_seek(), which should be used if, for example, you want to loop the result set again, see:

• http://php.net/manual/en/mysqli-result.data-seek.php#85629

In order to get the full result set, you could use mysqli_fetch_all(), but this is available only if you're using the mysqlnd driver (MySQL Native Driver), which should be the default setting for PHP 5.4 and above:

• https://dev.mysql.com/downloads/connector/php-mysqlnd/
• http://php.net/manual/en/book.mysqlnd.php
• http://php.net/manual/en/mysqlinfo.library.choosing.php

With PHP 5.3 and lower, the default is the libmysqlclient.

Hi,

how you are trying to add the feature: by adding code to the component source? When you say:

I cant access the component in the admin

you mean that the changes to the component code don't apply? Or that you cannot upload & install the component through the Joomla interface?

Hi,
you could list the items in the head and body elements and then, if you catch the style child, you remove it. This seems to works fine for me:

<?php

/**
 * Remove tag from DOM.
 * 
 * @see http://php.net/manual/en/domnode.removechild.php
 *      Read the comments about the loops
 * @param  DomDocument $doc
 * @param  string      $tag
 * @param  string      $element
 * @return void
 */
function removeTag(DomDocument $doc, $tag, $element = 'body')
{
    $section = $doc->getElementsByTagName($element)->item(0);
    $tag = $section->getElementsByTagName($tag);

    if($tag->length > 0)
    {
        foreach($tag as $node)
            $data[] = $node;

        foreach($data as $n)
            $section->removeChild($n);
    }

    return ;
}

$file = 'index.html';
$doc  = new DOMDocument();
$doc->loadHTMLFile($file);

removeTag($doc, 'style', 'head');
removeTag($doc, 'style', 'body');

$doc->saveHTMLFile($file);

You need to use a loop because:

You can't remove DOMNodes from a DOMNodeList as you're iterating over them in a foreach loop.

See the documentation for more notes:

• http://php.net/manual/en/domnode.removechild.php

Sorry, due to the PHPMailer tag I thought you were using the library.

From what I see you're using fsockopen() which works, but depending on the authentication mode your SMTP client must be required to reply to the server, before actually sending the message and, if this does not happen, then it will throw an error and close the connection. For this reason you should check the SMTP server logs.

If you can't, then try to use the linked library, it handles multiple authentication methods and it allows to debug easily.

What's the default for the Authorisation column? It could be:

-- case 1
`Authorisation` VARCHAR(40) DEFAULT NULL

-- case 2
`Authorisation` VARCHAR(40) DEFAULT ''

I don't know how MS Access manages when the default is not defined. But following the above example: in first case when you omit the value then the column will be NULL, if you insert some data and then update to remove it, then it will be an empty string, to get NULL on update you MUST set NULL. In the second case when you insert the data and omit the value, it will be an empty string, if you update with some data and then remove it, it will be an empty string, as expected. But you could also set it to NULL.

I am unable to send a mail. Please help me to fix the issue.

Hi, unable in which sense? The script fails the connection with the SMTP or something else? Do you have PHP error logs or SMTP error logs?

Replace lines below and including 47 with:

if(!$mail->Send()) {
    echo "Mailer Error: " . $mail->ErrorInfo;
} else {
    echo "Message sent!";
}

You should get some additional information about the error. You can also raise the debug level through $mail->SMTPDebug = 2;.

Some notes about the debugging with PHPMailer:

• https://github.com/PHPMailer/PHPMailer/blob/master/docs/Note_for_SMTP_debugging.txt

Independently for each user?

You're welcome!

Have you tried this script and looked at the database?

No, I don't need to test this.

Fix the database connection string:

$dbh = new PDO("mysql:host=$hostname;$dbname", $username, $password);

It's missing the dbname= attribute, It should be:

$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);

Also, to raise the exceptions you must set the attribute right after you define $dbh:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The default behaviour is PDO::ERRMODE_SILENT:

• http://php.net/manual/en/pdo.error-handling.php

Hi, you're missing the closing parenthesis in the VALUES() statement and the commas to separate the data:

$count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi' 'roy'");

Should be:

$count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi', 'roy')");

Ok, first of all: your UPDATE query had ; after timeIn and timeOut columns, due I think, to a copy & paste:

`timeIn`= '".mysql_real_escape_string($timeIn).";,
`timeOut`= '".mysql_real_escape_string($timeOut).";,

It would raise an error, even by using mysql_ functions.
To convert your UPDATE query to prepared statements you can do like this:

$query = "UPDATE `attendance` SET `present`= ?,`late`= ?, `timeIn`= ?, `timeOut`= ?, `remarks`= ? WHERE `id` = ?";

$stmt = mysqli_prepare($link, $query);
mysqli_stmt_bind_param($stmt, 'sssssi', $present, $late, $timeIn, $timeOut, $remarks, $id);

if(mysqli_stmt_affected_rows($stmt) > 0)
    echo "success";

else
    echo "failure";

mysqli_stmt_close($stmt);

BUT you have to consider that if you run the same query again, sending the same input that is already in the table row, then mysqli_stmt_affected_rows() will return 0. In particular read:

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE/DELETE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query has returned an error. NULL indicates an invalid argument was supplied to the function.

And:

If the number of affected rows is greater than maximal PHP int value, the number of affected rows will be returned as a string value.

Source: http://php.net/manual/en/mysqli-stmt.affected-rows.php

What is the purpouse of the SELECT query at line 99?

$query = "SELECT * FROM `attendance` WHERE `id`='$id'";
$query_run = mysql_query($query);

It seems useless as the variables are rewritten by the …

In addition to RudyM's comment, you're mixing mysql_ functions with mysqli_ functions. The formers are deprecated in PHP 5.* and removed in PHP 7.*, which is the latest stable PHP version.

So, you have to fix your queries at lines 99 and 102 and you have to fix the escaping, consider using prepared statements. Also, the return statement at line 17 of the connect file, will prevent the execution of the following code in the file, in practice mysqli_close($link) at line 19 is not executed, which in this case is fine, otherwise the queries on the main file would not work.

Hi, can you paste just the relevant code? People here can answer also from mobile and cannot setup a database and run your scripts. Also, if the download link becomes unavailable, the thread becomes useless for the readers.

Are you using event.preventDefault()?

Docs: https://api.jquery.com/event.preventdefault/

Hi, I'm not sure I've understood: line 26 of check.php is line 1 of the other code block?

Have you tried with DateTime?

<?php

$one = '09:30 am';
$two = '11:00 AM';

$time1 = new Datetime($one);
$time2 = new Datetime($two);

$diff = $time1->diff($time2);

print_r($diff);

Which outputs a DateInterval object like this:

DateInterval Object
(
    [y] => 0
    [m] => 0
    [d] => 0
    [h] => 1
    [i] => 30
    [s] => 0
    [weekday] => 0
    [weekday_behavior] => 0
    [first_last_day_of] => 0
    [invert] => 0
    [days] => 0
    [special_type] => 0
    [special_amount] => 0
    [have_weekday_relative] => 0
    [have_special_relative] => 0
)

From there, then it should be easy to perform the statements. Docs:

• http://php.net/manual/en/class.datetime.php
• http://php.net/manual/en/class.dateinterval.php
• http://php.net/manual/en/dateinterval.format.php

Yes, it can be done by getting the head element, then by creating an element link, appending the attributes to the created element, and finally appending the new element to the head section.

An example:

<?php

$file = './index.html';

$doc = new DomDocument();
$doc->loadHTMLFile($file);

$head = $doc->getElementsByTagName('head')->item(0);
$link = $doc->createElement('link');

$data = [
    'rel'  => 'canonical',
    'href' => 'http://link.tld/'
    ];

foreach($data as $key => $value)
{
    $attr = $doc->createAttribute($key);
    $attr->value = $value;

    # append the attribute to the element
    $link->appendChild($attr);
}

# append the element to the head section
$head->appendChild($link);

# save the file
$doc->saveHTMLFile($file);

Docs:

• http://php.net/manual/en/domdocument.loadhtmlfile.php
• http://php.net/manual/en/domdocument.createattribute.php
• http://php.net/manual/en/domnode.appendchild.php
• http://php.net/manual/en/class.domnode.php

Hi, you can explode() by comma and trim(), try:

$tags = 'cats, funny, hair cut, funny hair cut';
$result = array_map('trim', explode(',', $tags));

LAST_INSER_ID() without arguments will return the last inserted id, if you pass the autoincrement key, during an update query, then it will return it, as in my example. This is how you get the id of the updated row in MySQL. Check the examples here:

• http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

Bye!

If the update is limited to a single row, as in your example query, you could add rec_id = LAST_INSERT_ID(rec_id) to register the value to the function and then select it, for example:

UPDATE tier_".$tier_num."
SET payer_ID = '".$payer."', sub_ID = '".$payer_sub."', create_date = '".$created."',  status = 'I', rec_id = LAST_INSERT_ID(rec_id)
WHERE status = 'V'
ORDER BY rec_ID asc
LIMIT 1

And then just run:

SELECT LAST_INSERT_ID() as `rec_id`;

Yes, you can, just select and use the + operator:

select `column_a`, `column_b`, `column_a` + `column_b` as `column_c` from `table_name`;

Live example: http://sqlfiddle.com/#!9/935da4/1

Hi,

after if($rows==1){ fetch the row and check the column value that defines the role, use an IF statement to define the session and redirect, something like:

if($rows==1)
{
    mysql_data_seek($result, 0);
    $row = mysql_fetch_assoc($result);

    # admin
    if($row['role'] == 1)
    {
        $_SESSION['role'] = 1;
        header("Location: /admin/");
        exit;
    }

    # user
    $_SESSION['role'] = 0;
    header("Location: /");
    exit;
}

Make sure, on admin side to check the session value, otherwise a simple user could access the restricted area.

Also, switch to MySQLi or PDO, the MySQL api is now removed in PHP 7:

• http://php.net/manual/en/mysqlinfo.api.choosing.php

Hi, you can use the addCustomHeader() method, for example:

<form method="POST">
    <input type="text" name="companyName">
</form>

on PHP side you do:

$companyName = sprintf("CompanyName: %s", $_POST['companyName']);
$mail->addCustomHeader($companyName);

Check the syntax at:

• http://phpmailer.worxware.com/?pg=methods

And remember always to sanitize the input.

Yes I know css.. Do you have any solution?

Yes, using background-image for example, but I cannot suggest you the perfect solution as I haven't seen your design, nor I know how do you want the page to work with different window sizes and display resolutions.

Hi, do you know about cascading style sheets?

All of my other databases in there are accepting my permissions, just not this one.

Each database has a different folder, if for some reason in that folder there is something different from table files or the folder permissions changed, then the server will not remove it. Read this for more information about such issues:

• https://bugs.mysql.com/bug.php?id=62443

So, use the Windows 7 search tool to find this folder enfold@002d2016, open the path, it should be the folder that holds the tables of the enfold-2016 database. Once you're sure it's related to the database, right click the folder and delete it. Then restart the database service and check if it works fine.

As suggestion: before doing such operations, be sure to have a backup of the databases.

Hi,

it seems related to the permissions of OS user that is running the MySQL server, it seems this user cannot delete the directory that stores the database. Try to restore the permissions and, to remove the database, check the PHPMyAdmin data directory to manually remove this specific directory.

Hi,

I think you're confused by my previous post. The input type time is not boolean, so to see the value you must set it in the value attribute, so instead of:

<td><input type="time" name="timeIn" id="timeIn" <?php echo $row['timeIn'] > 0 ? ' time':''; ?>></td>

Do:

<input type="time" name="timeIn" id="timeIn" value="<?php echo is_null($row['timeIn']) === FALSE ? $row['timeIn']:''; ?>">

Your previous approach works if you're using a input types like checkbox, radio with the checked attribute or select tags with the selected attribute.

Hi,

try to submit the WHERE condition as a string:

$where = "new_col = 0xD9E6762DD1C8EAF6D61B3C6192FC408D4D6D5F1176D0C29169BC24E71C3F274AD27FCD5811B313D681F7E55EC02D73D499C95455B6B5BB503ACF574FBA8FFE85";
$this->db->get_where("user_stats_backup", $where);

Hi

And also, the checkbox says it's all ticked even the values are different (lines 61 - 63 of index.php)

what do you mean?checked is a boolean attribute, which means:

A number of attributes are boolean attributes. The presence of a boolean attribute on an element represents the true value, and the absence of the attribute represents the false value.

If the attribute is present, its value must either be the empty string or a value that is an ASCII case-insensitive match for the attribute's canonical name, with no leading or trailing whitespace.

The values "true" and "false" are not allowed on boolean attributes. To represent a false value, the attribute has to be omitted altogether.

Source: https://developers.whatwg.org/common-microsyntaxes.html#boolean-attribute

so in order to set a checkbox you have to verify the value returned by the row and decide if you want to include the attribute, for example:

<input type="checkbox" name="present" id="present"<?php echo $row['present'] > 0 ? ' checked':'';?>>

Look specifically at:

• https://bryanjhvtk.wordpress.com/2014/03/14/functions-autoloading-php/

It was suggested in that discussion: he's using namespaces to autoload function helper files, basically you create an helper file for each custom function and then you use a class to load it. Live example here, with few fixes:

• http://code.runnable.com/Vx_p39a6FuIlKR2c/testing-autoload-for-functions-for-php

In practice, after spl_autoload_register(); you declare which namespaces you want to use and then you can start calling your functions:

<?php

    spl_autoload_register();

    use php\helpers\Fn;
    echo Fn::custom_function('Hello');

A part that loader class you don't need to do OOP.

And $sizes will be an array, correct? From your previous code I see you set two different variables: $sArray or an empty $sizesArray. You can save an array into a table column, but you have to convert it to a string:

• through $data = implode(',', $sizes); to generate a CSV list;
• or to a JSON string through $data = json_encode($sizes);;
• or use $data = serialize($sizes);.

An alternative is the dynamic column available in MariaDB, a MySQL fork, which allows you to store data as JSON in a blob column type, for example:

<?php

    $conn = require 'pdo.php';
    $sizes = [123, 456, 789];
    $quantities = [17, 23];

    $stmt = $conn->prepare('INSERT INTO `test` SET `features` = COLUMN_CREATE(?, ?, ?, ?)');
    $stmt->execute(['sizes', implode(',', $sizes), 'quantities', implode(',', $quantities)]);

Which then looks like:

SELECT COLUMN_JSON(`features`) AS `features` FROM `test`;
+----------------------------------------------+
| features                                     |
+----------------------------------------------+
| {"sizes":"123,456,789","quantities":"17,23"} |
+----------------------------------------------+
1 row in set (0.00 sec)

And allows a bit more flexibility when storing variable attributes like above. Docs: https://mariadb.com/kb/en/mariadb/dynamic-columns/

It would be a lot easier by using an autoload script, namespaces and some classes or some helper files.

For some ideas, read: http://stackoverflow.com/questions/4737199/autoloader-for-functions

Try to replace disabled with readonly:

• https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input#attr-readonly

But if it is for security, don't rely on this solution, as one can easily edit the form and submit arbitrary input. Please, reply with a post not with a comment, thanks.