Jump to content

DavidAM

Staff Alumni
 View Profile  See their activity

  • Posts

    1,984

  • Joined

  • Days Won

    10

 Content Type 

Posts posted by DavidAM

    query for postgresql - Extract the number on the title and order by number

    in PostgreSQL

    Posted

    I don't know Postgresql. The problem you're going to have is that 1.10 is less than 1.9, In fact, it is equal to 1.1.

    If the Cast-Substribg works (again, I don't know Postgres), you should do it twice to split the version number. And order by the two values. Something like this 

    CAST ( SUBSTRING ( movies.title FROM '#(\d+)\.\d+' ) AS INT ) AS major, 
CAST ( SUBSTRING ( movies.title FROM '#\d+\.(\d+)' ) AS INT ) AS minor

...

ORDER BY major, minor

    You can use Integer now, instead of Decimal.

    • Thanks 1

    Push key value in multidimensionnal array in loop php

    in PHP Coding Help

    Posted

    Please use the code tags - the "<>" in the formatting bar. It makes the code easy to read and work with.

    You need to capture the index of the child you add in LOOP 2 and use that index to add the children in LOOP 3 

    # LOOP TWO
# ...
$data1 = array('id' => $row1['id'], 'name' => $row1['name'], 'title' => $row1['title'] , 'relationship' => '111');
$data_cst["children"][] = $data1; 
$index1 = count($data_cst["children"]) - 1;	// <== ADD THIS LINE
    # LOOP THREE
# ...
$data2 = array('id' => $row2['id'], 'name' => $row2['name'], 'title' => $row2['title'] , 'relationship' => '111');

## ????????? // CHANGE THIS TO
$data_cst["children"][$index1][] = $data2;

    Running queries inside a loop is generally not a good idea. If you know you are going to go 3 levels (or some other fixed number), you can do this all with a single query and then build your array.

    Also, do not select everything from the table. Select only the columns you want/need.

    You  might try something like this. I have not tested it, but I think it will come close. 

     <?php
	require_once 'pdo.php';
	$data_cst = array();
	// FIRST LOOP
	try{
		$sql = 'SELECT L1.id AS L1id, L1.name AS L1name, L1.title AS L1title,
					L2.id AS L2id, L2.name AS L2name, L2.title AS L2title,
					L3.id AS L3id, L3.name AS L3name, L3.title AS L3title
				FROM main AS L1 
					LEFT JOIN main AS L2 ON L2.parent_id = L1.id
					LEFT JOIN main AS L3 ON L3.parent_id = L2.id
				WHERE L1.level = 0';
		$result = $pdo->query($sql);

		if($result->rowCount() > 0){
			while($row = $result->fetch()){
				$L1id = $row['L1id'];
				if ( ! isset($data_cst[$L1id])) {
					// If this is the first row for the L1id, create the data for it
					$data_cst = array('id' => $row['L1id'], 'name' => $row['L1name'], 'title' => $row['L1title'] , 'relationship' => '001', 'children' => []);
				}

				if ( ! empty($row['L2id'])) {
					// We have data for a child of L1id
					$L2id = $row['L2id'];
					if ( ! isset($data_cst[$L1id]['children'][$L2id])) {
						$data_cst[$L1id]['children'][$L2id] = array('id' => $row['L2id'], 'name' => $row['L2name'], 'title' => $row['L2title'] , 'relationship' => '111', 'children' => []);
					}

					if ( ! empty($row['L3id'])) {
					// We have data for a child of L2id
					$L3id = $row['L3id'];
					$data_cst[$L1id]['children'][$L2id]['children'][$L3id] = array('id' => $row['L3id'], 'name' => $row['L3name'], 'title' => $row['L3title'] , 'relationship' => '111');
					}
				}
			}
		} else {
			echo "No records matching your query were found.";
		}
	} catch(PDOException $e){
		die("ERROR: Could not able to execute $sql2. " . $e->getMessage());
	}

	echo json_encode($data_cst);

    Notes:

    • Don't bother closing query results or the database connection. PHP will handle it when the script ends
    • I indexed the "children" array with the associated ID instead of letting PHP assign something. This made it easier to add the next child or sibling

     

    Converting and Calculating Numbers (decimal & rounding)

    in PHP Coding Help

    Posted

    7 hours ago, martinspire said:

    Once I've done that, I need to calculate the square metres (900mm x 1500mm / 1000 was my first thought) but that raised the second thing; adding a decimal point in the result. 900 x 1500 / 1000 = 1350 but I need the result to be 1.350

    Your math is off. There are 1,000 mm in a meter. But there are 1,000,000 square mm in a square meter. You should divide by 1,000,000 if you use the approach in your original post.

    900mm * 1500mm / 1000000 = 1.35 sq meters

    • Like 1

    Problem with Xampp local Server

    in PHP Installation and Configuration

    Posted

    My guess is your XAMP configuration is pointing to the wrong folder for Document Root.

    Looking at your File System image, I see a folder named "public". This is often the name of the document root. Perhaps your AMP configuration is pointing to SchoolApp-master when it should be pointing to SchoolApp-master/public.

    In the public folder there should be an index.php file and a .htaccess file. There is probably also a folder in there called auth.

    • Like 1

    Notice: Undefined offset: 2013

    in PHP Coding Help

    Posted

    That's the puzzling thing - according to the last four lines in the code (initial post) they are quoted :confused:

    I found it puzzling, too. I was too lazy to try from the database, but this is interesting:

     

    <?php
error_reporting(-1);
ini_set("display.errors", 1);

/* 
	Test string indexes in array: http://forums.phpfreaks.com/topic/301470-notice-undefined-offset-2013/#entry1534467
*/

printf('PHP Version: %s' . PHP_EOL, phpversion());
printf('uname: %s' . PHP_EOL, php_uname());
echo PHP_EOL;

$res = array(
	array( 	'Row' => 'First',  (string) 2013 => 30.5, '2014' => 42, "2015" => 8 ),
	array( 	'Row' => 'Second', (string) 2013 => 11.5, '2014' => 42, "2015" => 6 ),
	);

foreach($res as $row) {
	printf('%6s:  %.2f  %d  %d' . PHP_EOL, $row['Row'], $row['2013'], $row['2014'], $row['2015']);
	printf('%6s:  %.2f  %d  %d' . PHP_EOL, $row['Row'], $row["2013"], $row["2014"], $row["2015"]);
	printf('%6s:  %.2f  %d  %d' . PHP_EOL, $row['Row'], $row[2013],   $row[2014],   $row[2015]);
	printf('%6s:  %.2f  %d  %d' . PHP_EOL, $row['Row'], $row[(string) 2013],   $row[(string) 2014],   $row[(string) 2015]);
	printf('%6s:  %.2f  %d  %d' . PHP_EOL, $row['Row'], $row[strval(2013)],   $row[strval(2014)],   $row[strval(2015)]);
	echo PHP_EOL;
}

echo PHP_EOL . 'SERIALIZED: ' . PHP_EOL;
echo serialize($res);
echo PHP_EOL;

echo PHP_EOL . 'VAR_DUMP: ' . PHP_EOL;
var_dump(array_keys($res[0]));
echo PHP_EOL;

/* RESULTS

PHP Version: 5.5.12
uname: Windows NT XXXXXX 6.1 build 7601 (Windows 7 Business Edition Service Pack 1) i586

 First:  30.50  42  8
 First:  30.50  42  8
 First:  30.50  42  8
 First:  30.50  42  8
 First:  30.50  42  8

Second:  11.50  42  6
Second:  11.50  42  6
Second:  11.50  42  6
Second:  11.50  42  6
Second:  11.50  42  6


SERIALIZED: 
a:2:{i:0;a:4:{s:3:"Row";s:5:"First";i:2013;d:30.5;i:2014;i:42;i:2015;i:8;}i:1;a:4:{s:3:"Row";s:6:"Second";i:2013;d:11.5;i:2014;i:42;i:2015;i:6;}}

VAR_DUMP: 
array(4) {
  [0] =>
  string(3) "Row"
  [1] =>
  int(2013)
  [2] =>
  int(2014)
  [3] =>
  int(2015)
}

*/
    The different types of string quoting/coercing, makes no difference.

     

    PHP is implicitly converting the associative keys to integers. Which does not matter when I reference them because of the loose data-typing in PHP.

     

    My guess is that the database driver is setting the array keys (internal representation) as strings. The "solution" works (I guess) because the odbc->result() call expects a column name to be a string. The array reference (in the OP) was converted to an integer since that's how PHP (internally) handles them (when the key is composed of only digits).

     

     

    The other interesting thing is: var_dump() on this machine, would not dump $res. Or I should say, it produced

    array(2) {
  [0] =>
  int(0)
  [1] =>
  int(1)
}
    I don't use var_dump() much - I usually use print_r() - unless I need to see the datatypes. So, maybe that's normal?
    • Like 1

    seo url article page links issue

    in PHP Coding Help

    Posted

    The user's browser (all browsers) need an absolute url to request something from the Internet. When the browser gets a relative url, it uses the current page to determine the absolute url for the request. Since you are doing rewrites on the server side the browser thinks the current url is www.yourdomain/article/10/this-is-something, so it drops the "this-is-something" (because that's the "file" name) and keeps the rest as the "directory" name; then tacks the relative url on the end - so it has www.yourdomain/article/10/index.php.

     

    In short, change the relative urls, to absolute urls. You don't have to include the domain, just put a slash at the front: /index.php since that goes to the document root for the current domain.

    Issue with contact form within Word Press

    in PHP Coding Help

    Posted

    There are two things you need to change:

     

    $headers = 'From: '.$name.' <'.$email.'>';
// Change to
$headers = 'Reply-To: '.$name.' <'.$email.'>';

    Your server is (most likely) not authorized to send mail "From" whatever is in $email. Sending mail from "yourDomain.com" and saying it is from "someOtherDomain.com" will appear as a forgery and likely get you SPAM points (in this case the email with the fewest points, wins). Mail from your server needs to say it is from your server, and the server/PHP should be configured that way. The "Reply-To" header will allow the recipient to reply to the $email, which is probably what you are trying to accomplish there.

     

    mail($email_to, $subject, $message, $headers);
$sent = true;

// Change to

$sent = mail($email_to, $subject, $message, $headers);

    You are arbitrarily saying the email was sent. mail() will return false if it cannot submit it to the sendmail system. That would be a server configuration issue. However, just because you get true from mail() does not mean the mail went out. It just means it was handed to the server's sendmail system. So, if this returns false, check the server's configuration. If it returns true, and you still don't get it, look at the sendmail configuration and (to some extent) the PHP configuration.

     

    Always check your Junk mail/Spam filters when looking for mail from PHP. It is possible there are headers missing or present that will get the mail flagged or discarded.

    • Like 1

    need to Amendment the Code: it Duplicate records

    in PHP Coding Help

    Posted

    What records do you think are duplicate? I don't see any.

     

    I see a Cartesian Product between the Infractions and Actions table. There is no relationship and no join between these tables, so every row for Student 7 in Infractions is associated with every row in Actions for Student 7. If an Action is not intrinsically associated with a specific Infraction, then you cannot report them that way. If they are associated, then the database design needs to be fixed.

     

    Concerning Student #7 ("ahmad") is Actions #1 ("Actions 1") associated with Infractions #1 ("infractions 1") or Infractions # 2 ("infractions 2")? Is Actions #2 ("Actions 2") associated with "infractions 1" or "infractions 2"? Your database design does NOT provide any relationship (association) other than the Student ID. So, all Infractions for Student 7 are associated with all Actions for Student 7 -- and that is EXACTLY what the output is telling you.

    need to Amendment the Code: it Duplicate records

    in PHP Coding Help

    Posted

    There is no relationship between Actions and Infractions. Therefore all Actions (for a student) are associated with all Infractions (for that student) - a Cartesian product.

     

    The relation between Students and Infractions is one-to-many (one Student can have many Infractions). If a single Infraction can have only a single Action, I would eliminate the Actions table and put the Action_Text in the Infractions table -- leave it NULL until the action is applied. If an Infraction could result in one or more Actions (or you really want/need a separte table), the Actions table should be realted to the Infractions table NOT the Student table.

    PDO Prepare

    in PHP Coding Help

    Posted

    Hello,

     

    I am pretty new to PDO but have heard that it is good to use prepared statements to help avoid mysql injections.

     

    What I'm wondering is, when using prepare, does one need to bind parameters or would one be able to do something like the following without risking security?

    $db = new PDO(..);

$r = $db->prepare("SELECT * FROM test WHERE col=$_POST['col']");
$r->execute();
    Thanks

     

    @pwntastic I'm glad you asked the question. The short answer is: no, only the bound data (none in your example) is protected from SQL injection.

     

     

    DavidAM climbs up on his soap-box

     

     

    Prepared statements do NOT automatically protect you from SQL injections. And I wish people would quit suggesting them for that purpose. If we are going to suggest that, we may as well suggest turning MAGIC QUOTES back on!! It amounts to the same thing -- a false sense of security. The proof is in the OP's original question:

     

    Prepared statements are not about security. They were invented - years and years ago - for the purpose of improving performance on queries run in a loop. There are two stages of execution at the database server for a query. 1) Build the query execution plan; and 2) Execute the plan (with specific data). When it was necessary to execute a query in a loop, the "plan" for that query was built every time. Prepared statements were invented so the plan could be built once, and executed multiple times.

     

    I am NOT advocating queries in a loop. The RDBMS is built on Set Theory, and it will always be more efficient to execute a single query to affect multiple rows, when possible. But sometimes it is necessary to use a loop to execute the same query, with the calculations for the data being too complex or not available to put in a single query statement. For this we have Prepared Statements.

     

    Read the manual:

     

     

    The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

    http://us1.php.net/manual/en/mysqli.quickstart.prepared-statements.php (emphasis added)

     

     

     

    The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.

    http://us1.php.net/manual/en/pdo.prepared-statements.php (emphasis added)

     

    When true server-side prepared statements are used, it takes two round-trips to the database. When a non-prepared statement is executed, it takes only one round-trip. Using prepared statements exclusively increases resource use.

     

    The PDO page also says:

     

    The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

    (emphasis added)

     

    The automatic quoting of the bound data is a side-affect of the prepared statement. And we need to clearly state that only the bound data is protected from SQL injection. Otherwise, we are giving a false sense of security; the same false sense of security that many PHP coders had when using magic_quotes - which has been removed from the language because of the false sense of security.

     

     

    DavidAM climbs off his soap-box

    Need Help With Logic

    in PHP Coding Help

    Posted

    When asking for help with a warning/error, you need to post the entire error message here. It will help us help you more quickly (and correctly).

     

    My guess is that $company['contacts'] and $company['branches'] are not arrays at the point that you get the warning. If you look closely at your collection code, when you first spot a company you don't include those sub-arrays in the company creation:

     

        if(!isset($company_array[$row['company_id']]))
    {
        //Add the company specific data here
        $company_array[$row['company_id']] = array(
            'name'    => $row['comp_name'],
            'address' => $row['address'],
            'phone'   => $row['phone'],
            'fax'     => $row['fax'],
            'web'     => $row['web'],
            'email'   => $row['email'] ,
// Add these lines AND THE COMMA above
'contacts' => array(),
'branches' => array()
        );
    In addition, during the branch collection you have a small logic error:

        $compID = $row['company_id'];
    $branchID = $row['branch_id'];
// YOU SHOULD HAVE $compID in the first subscript - where you have ''
    if(!isset($company_array['']['branches'][$row['branch_id']]))
    {
        $company_array[$compID]['branches'][$branchID] = array(
    Also, since you grabbed the $compID and $branchID, why not go ahead and use them in that if statement?

    Link input with a form?

    in HTML Help

    Posted

    No, you cannot overlap or nest FORMs in HTML. If you need the quantity in both forms, and it needs to be the same value, you will need to use JavaScript.

     

    Put a hidden input field in the second form to hold the quantity. Then either (1) add an event (JS) to the other form's quantity field so when it changes, the hidden field is updated with the new value; or (2) and an onSubmit event to the paypal form to retrieve the current value from the other quantity field and put it in the hidden field (before sending).

    Link input with a form?

    in HTML Help

    Posted

    The <FORM> tags do not have any affect on the layout of the page. It is really just a container for form fields. You just need to move the opening tag above your first form field.

     

    Be aware, if there are other form fields on the page (not shown in the code you posted), they will become part of the form and will be sent when the form is submitted.

    Invalid parameter number: number of bound variables does not match number of tokens

    in PHP Coding Help

    Posted

    ... So, the following sql syntax should be correct using mysql (never tested) 

     

    INSERT tbl_name VALUE ('val_1','val_2',.....)

    That is valid, as long as you include a value for EVERY column in the table.

     

     

    For the record, I have used the INSERT ... SET ... syntax on occasion. If you are building queries dynamically, you can build up the column = value list and then add it to an INSERT or UPDATE statement. So, you only need one process to build the query regardless of which statement you are building.

    QUICK help with making a variable a link

    in PHP Coding Help

    Posted

    It would help us to help you if you would tell us what the error message says.

     

    I don't think that is valid code. You start a PHP assignment and drop out of PHP.

    You are also echoing values, they are going to go to the output, NOT the variable assignment.

    You should just concatenate the variables into a string; or, my personal preference:

     

    $link = sprintf('<a href="post?id=%s&title=%s">%s</a>', $id, $slug_title, $title);
    • Like 1

    Problem with Login form.

    in PHP Coding Help

    Posted

    It is not clear which Database library you are using. If it is mysqli, then the query() method is going to return a result object (unless the query fails). NOTE that a query that finds ZERO rows has NOT failed. You need to check to see if there was exactly one row returned in order to determine if the credentials are valid.

     

    You NEED to escape the user inputs BEFORE putting them in your query. Your current query is subject to SQL Injection.

    Special language characters doesn't display correctly

    in MySQL Help

    Posted

    Do not use SET NAMES. This is a major security vulnerability, because it silently changes the character encoding without notifying PHP. As a result, critical functions like mysql_real_escape_string() may no longer work, leaving the application wide open to SQL injection attacks.

     

    The proper way to change the connection encoding is to call mysql_set_charset().

    I stand corrected. I swear I don't remember seeing that function before. For some reason, I thought the mysql library didn't have a specific charset function, but I was switching to mysqli when I first had the need for it, so I guess I missed it.

    • Like 1

    Special language characters doesn't display correctly

    in MySQL Help

    Posted

    1. Make sure the character set on the table is UTF-8: CREATE TABLE ... CHARACTER SET UTF-8

     

    2. Make sure the HTML document is UTF-8 - You've got that: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

     

    3. Make sure the database connection is using UTF-8: mysql_query("Set names 'utf8'");

     

    NOTE: The (PHP) mysql library has been deprecated. All new code should be using the mysqli library (which sets the character set differently).

    • Like 1

    Grouping by column to get the oldest entry

    in MySQL Help

    Posted

    I want to group a bunch of rows together

    Define "a bunch"

     

    and while you're at it, define "group ... rows" (based on what?)

     

    to get the oldest entry which is a unix time stamp

    MAX(table.replyUnixTime) will get the youngest entry; since Unix time is constantly increasing, the MAX value will be the most recent.

     

     

    I don't think anyone can say definitively what they query you presented will return. The GROUP BY will return a single row for each DISTINCT value of replyID in the table. The SELECT * will return all columns in a row. But since there are multiple rows with the same replyID and there is no selection criteria, there is no way to know which row is selected to "return all columns".

     

    We really do need to know what the table structure is, and what it is you want from the table in order to provide any assistance at all.

    Array push in foreach loop

    in PHP Coding Help

    Posted

    To get what you are asking for: ok64|33|37|Test1|Test2|Test3|

     

    $check = array();
foreach($results as $rows) {
  $check[$rows['votes']] = $rows['value'];
}

$out = 'ok' . implode('|', $check) . '|' . implode('|', array_keys($check)) . '|';
echo $out;
    That's probably not the way I would send that data, though. JSON is capable of representing arrays and objects. So I would probably just json_encode the $check array instead of building the string. But I'm not a JavaScript guru, so I can't explain how to use it from there.

     

    How do you know (in JS) which part of the string is votes and which is values? Why not just send those two arrays from PHP?

     

    $out = array('votes' => array(), 'values' => array());
foreach($results as $rows) {
  $out['votes'][] = $rows['votes'];
  $out['values'][] = $rows['values'];
}

echo json_encode($out);
    That should give you (in JS) an object with two array properties (votes and values), then you don't need to split them in JS.

    Creating Triggers in PhpMyAdmin (Beginner Questions)

    in MySQL Help

    Posted

    Assuming we are talking about MYSQL:

     

    1. No - Unless something has changed recently, you can NOT update a table in the table's trigger. You could use a BEFORE UPDATE trigger to

    SET NEW.right_percent = NEW.right_count/(NEW.right_count + NEW.wrong_count);
    However, see Caveat below

     

    2. Triggers are not exported with the data unless you explicitly ask to have them exported. I don't use phpMyAdmin much, I generally use the command line. You have to explicitly tell mysqldump to include the triggers.

     

    3. I don't think phpMyAdmin provides a way to see them.. From the mysql command line, the command is

    SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]
    You could run that command in a (phpMyAdmin) query window.

     

     

    Caveat

    You really should NOT store a calculated field in the database. Calculate the percentage when you SELECT the data.

     

    SELECT ... right_count, wrong_count, right_count + wrong_count AS Total_Count, 
right_count / (right_count + wrong_count) AS Right_Percent
...
    Also, be sure your updates are autonomous. That is, don't do this:

    # BAD CODE
# Select from table and store in local variables
$right = $row['right_count'];
$wrong = $row['wrong_count'];

# Update local variables
if ($user_is_right) $right += 1;
else  $wrong += 1;

# Update the database
UPDATE table SET right_count = $right, wrong_count = $wrong ...
    This would overwrite any changes by another user between the SELECT and UPDATE by this user.

    Instead:

     

    $add_right = 0;
$add_wrong = 0;

if ($user_is_right) $add_right = 1;
else $add_wrong = 1;

UPDATE table SET right_count = right_count + $add_right, wrong_count = wrong_count + $add_wrong ...
    This would increase whatever value is NOW in the database, regardless of how many users updated since this user started.

    Finding extension of file that has been retrieved using CURL

    in PHP Coding Help

    Posted

    When you download a "file" using http, there is no actual file. You get a stream that you are then saving as a file. However, the server that you download from may send a header suggesting a name for the file. You need to retrieve the headers in the curl request, and inspect them for one that contains the suggested filename. You can then use this value as the name of the file when you save it.

     

    You will have to watch out for name conflicts, since there is nothing to prevent you from getting the same filename in multiple "downloads".

     

    You can use the CURLOPT_HEADER option to get the headers. But these will be included in the returned data ($data in your script). So you will have to remove the headers, inspect them, and extract the filename.

     

    I don't remember the name of the header, in fact, I don't think I have actually done this. So grab the headers and echo them to see what is there and what you need. The headers should end with two consecutive line endings ("\r\n\r\n"). Then you can explode on the line endings to get an array of headers.

     

    curl_setopt($ch, CURLOPT_HEADER, true);
$data = curl_exec($ch);
$pos = strpos("\r\n\r\n", $data); # Find the end of the headers
$headers = substr($data, 0, $pos); # Save headers
$data = substr($data, $pos+4); # Remove headers from data
$headers = explode("\r\n", $headers); # Explode headers to array
print_r($headers);
    Un-tested code - no error checking - UAYOR
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.